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

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:

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....

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.

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!