SQL Server Date Functions

Posted At : September 23, 2009 10:45 PM | Posted By : James Privett
Related Categories: SQL

In my quest to improve my SQL skills I wanted to get used to some of the date functions that can be used in SQL Server. year(), month(), day() Using the following SELECT statement I can extract the month from the date of birth field. I can then order the results by this data in order to get a month by month view of birthdays. The same could happen with year() and day()

view plain print about

1SELECT firstname, surname, month(dob) AS birthMonth, dob FROM yourTBL ORDER BY birthMonth

The results

Now my boss has seen this data he wants to run a query that will only return the users who's birthday's are in May. We can use the month() function in the WHERE clause to return this data.

view plain print about

1SELECT firstname, surname, month(dob) AS birthMonth, dob FROM yourTBL WHERE month(dob) = '5'

The results

dateAdd(datepart,number,date) Now my boss wants me to forge the data to show the selected users are in fact a year older than they really are. We can use the dateAdd() function to return these results.

view plain print about

1SELECT firstname, surname, DATEADD(year,1,dob) AS oneYearOlder, dob FROM yourTBL WHERE month(dob) = '5'

The results

convert(data_type(length),data_to_be_converted,style) Returning dates is all well and good but outputting them in a readable fashion is another matter. We can use the convert() function to convert the dates into a string.

view plain print about

1SELECT firstname, surname, CONVERT(VARCHAR(11),dob,106) AS dateOfBirth FROM yourTBL WHERE month(dob) = '5'

The results

dateDiff(datepart,startdate,enddate) Now my boss is getting very weird. He wants to know who long (in days) each user has been alive for. Here we can use the dateDiff() function.

view plain print about

1SELECT firstname, surname, DATEDIFF(day,dob,getDate()) AS daysOnPlanet FROM yourTBL WHERE month(dob) = '5' ORDER BY daysOnPlanet

The results