Resources For IT Professionals

# T-SQL: Date-Related Queries

In this article I plan to add various interesting date related queries. This article will expand when new problems will present themselves in the Transact-SQL forum.

# Finding Day Number from the Beginning of the Year

I want to start with this simple question that was posted today (May 31, 2013) - how to find today's date day number from the beginning of the year.

This is my solution and a bit of explanation at the end

`DECLARE` `@curDate ``DATE` `= ``CURRENT_TIMESTAMP``;`
`DECLARE` `@YearStart ``DATE` `= dateadd (`
`    ``year`
`    ``,datediff(``year````, ````'19000101'``, @curDate)`
`    ``,``'19000101'`
`    ``);`

`SELECT` `datediff(``day``, @YearStart, @curDate) + 1 ``AS` `[Number ``of` `Days ``from` `the ``Year` ``` Start]```

The @YearStart variable dynamically calculates the beginning of the year for any date based on the year difference with any known date we use as anchor date.

However, there is much simpler solution as suggested by Gert-Jan Strick in the thread I referenced:

`SELECT` `datepart(dayofyear, ``current_timestamp````) ````AS` ``` [Number ````of` ``` Days]```

# Finding Beginning and Ending of the Previous Month

Today's Transact-SQL MSDN forum presented the following problem Change date parameters to find data from previous month.

I will give my solution to this problem from that thread:

`DECLARE` `@MinDate DATETIME, @MaxDate DATETIME;`

`SET` `@MinDate = DATEADD(``month``, DATEDIFF(``month````, ````'19000201'``, ` `CURRENT_TIMESTAMP````), ````'19000101'``);`

`SET` `@MaxDate = DATEADD(``day``,-1,DATEADD(``month````, 1, @MinDate)) ````-- for versions prior to SQL 2012;`

`SET` `@MaxDate = EOMONTH(@MinDate); ``-- for SQL Server 2012 and up`

This entry participated in the Technology Guru TechNet WiKi for May contest and won the Bronze medal.
• Please add 2 and 1 and type the answer here:
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
• Naomi  N edited Revision 13. Comment: Added link

• Naomi  N edited Revision 10. Comment: Added See Also

• Naomi  N edited Revision 9. Comment: Added tense, link and new tag

• Naomi  N edited Revision 8. Comment: Added toc - need to add more cases

• Naomi  N edited Revision 6. Comment: Added day for the today link

• Ed Price - MSFT edited Revision 5. Comment: Title casing and clarified technology in the title. Feel free to refine. Great article, and great presentation of the code!

• Naomi  N edited Revision 4. Comment: Added tag

• Naomi  N edited Revision 3. Comment: Adjusted link

• Naomi  N edited Revision 2. Comment: Formatting

• Naomi  N edited Revision 1. Comment: Added Gert-Jan code

Page 1 of 1 (10 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
• Naomi  N edited Revision 1. Comment: Added Gert-Jan code

• Naomi  N edited Revision 2. Comment: Formatting

• Naomi  N edited Revision 3. Comment: Adjusted link

• Naomi  N edited Revision 4. Comment: Added tag

• Ed Price - MSFT edited Revision 5. Comment: Title casing and clarified technology in the title. Feel free to refine. Great article, and great presentation of the code!

• Naomi  N edited Revision 6. Comment: Added day for the today link

• In SQL Server 2012 you can program as:

SELECT DATEDIFF(dd, EOMONTH(current_timestamp,-MONTH(current_timestamp)), current_timestamp);

• Naomi  N edited Revision 8. Comment: Added toc - need to add more cases

• Naomi  N edited Revision 9. Comment: Added tense, link and new tag

• Naomi  N edited Revision 10. Comment: Added See Also

• Congratulations. Nice, brief and to-the-point, but also rich enough in detail to follow easily.

• Naomi  N edited Revision 13. Comment: Added link

Page 1 of 1 (12 items)