Today’s post is a very simple tip based on a question I received from one of my colleagues. They had dates stored in their database and wanted to extract day of the week and day of the year information from the date column.
Now, this would have been a very easy task for the team had this requirement been related to displaying the day of the week and day of the year information on the UI. But, in this particular case, they had to dump it out to a text file (using SQL Server BCP) for exchanging with a legacy reader-board feeding system (If you are unfamiliar with the term, the display boards on railway stations and airports that inform travellers about the schedule and other stages of the boarding process and the associated data stores are reader-board systems). When they approached me, they told me it was a big problem and were wondering if I could spend a day with them to help them figure things out. When I said it won’t even take more than 10 minutes, they were awestruck!
The DATEPART function
Most of us have worked with T-SQL date manipulation functions in Microsoft SQL Server, with the most common use being extracting parts like date, month and year from the date or a date/time value. However, there are many more options, which are what I shared with the team.
Besides extracting parts of a date, the DATEPART function can also help us determine the following for a given date or date/time value:
- The Day of the Week
- The Day of the Year
- Week number
- ISO Week Number
Here’s the associated query:
USE tempdb GO DECLARE @currentDate DATE = GETDATE() SELECT @currentDate AS CurrentDate, DATEPART(DAYOFYEAR, @currentDate) AS DayOftheYear, DATEPART(WEEKDAY , @currentDate) AS DayOftheWeek, DATEPART(WEEK , @currentDate) AS WeekNumber, DATEPART(ISO_WEEK , @currentDate) AS ISOWeekNumber GO /* DAYOFYEAR --Equivalent interval identifiers: DY WEEKDAY --Equivalent interval identifiers: DW WEEK --Equivalent interval identifiers: W ISO_WEEK --Equivalent interval identifiers: ISOWK, ISOWW */
For a test value of ‘2012-08-25’, the results are:
All the team had to do was to incorporate the DATEPART function call in their T-SQL queries. There – did it take more than 10 minutes?
If you would like to explore all the options of the DATEPART function, I would redirect you to the Books-On-Line page: http://msdn.microsoft.com/en-us/library/ms174420.aspx
Also, if you would like to read more about how the team exported bulk data from their SQL Server tables into a text/comma-separated file, you may refer my previous posts:
- BCP & Bulk Inserts – Underappreciated features of Microsoft SQL Server
- SSMS – Query Result configuration – Generate comma separated output without BCP or query changes
Until we meet next time,