Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with:

Ask Ben: Selecting Parts of a Date/Time Stamp In SQL

By Ben Nadel on
Tags: Ask Ben, SQL

How can I select the month part of date value in SQL server?

I get this type of question a lot: How can I select the hours from the date in SQL? How can I get the day of the year from a date? How can I get the day of the month from a date? For all of these, the easiest solution would be to use SQL Server's built in date/time function: DatePart(). It allows you to select individual parts of a date:

  • SELECT
  • id,
  • (
  • DATEPART(
  • mm, -- mm: Select only the month
  • birthday
  • )
  • ) AS birthday_month
  • FROM
  • girlfriends

It's a fairly straight forward function. You can of course do more than getting the months (as in the example). You can get all different parts of the date by specifying a different first argument:

year: yy, yyyy
quarter: qq, q
month: mm, m
dayofyear: dy, y
day: dd, d
week: wk, ww
weekday: dw
hour: hh
minute: mi, n
second: ss, s
millisecond: ms

You can learn more about this function at the MSDN website.



Reader Comments

Problem is, datePart is not recognized in CF Query of Queries. Any other simple way to get the same results? I need the day, month and year as seperate columns in my QofQ results.

Reply to this Comment

@Connie,

Not sure there is a nice way to do that in Query of Queries. You'd be better off performing that in the original query.

Reply to this Comment

hey thanks for this.... helped a lot...
just 1 quick question.Though am using "mm", if the month is less than 10, i get "m" i.e "1" or "2" ..something like that.Just wondering if there is a way i can get "01" "02" ...a two digited stuff....

Thanks once again for your help

Reply to this Comment

@Praveen,

I am not sure off-hand. Typically, I'll do that level of formatting in the ColdFusion side of things.

Reply to this Comment

This probably isn't the most elegant way to get a 2 digit formatted month in sql, but I've used something like this in the past. Convert the date to a string, then pull out the parts you need as a substring.

Select SubString(Convert(Char(10), GetDate(), 102), 6, 2)

Reply to this Comment

Hi Ben,

How can I select the entire date portion of a "dd/mm/yyyy hh:mm:ss AM/PM" date/time stamp? One catch is that the Len() function can't be used because sometimes the day, month, or any component of the time is one digit (i.e., no 'filler' zeros are used). I haven't been able to find any forum on how to extract the ENTIRE date portion of a date/time stamp - i.e. only removing the time. Any suggestions? Thanks in advance!

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.