Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (eMicrosoft Partne)
  • When: 31 May 2013 11:29 AM
  • Last revision by (eMicrosoft Partne)
  • When: 11 Sep 2013 12:19 PM
  • Revisions: 15
  • Comments: 12
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  T-SQL: Date-Related Queries

T-SQL: Date-Related Queries

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.

Table of Contents



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




See Also


This entry participated in the Technology Guru TechNet WiKi for May contest and won the Bronze medal. 
, , , , , , , , , [Edit tags]
Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 11 Sep 2013 12:19 PM

    Naomi  N edited Revision 13. Comment: Added link

  • 4 Sep 2013 2:44 PM

    Naomi  N edited Revision 10. Comment: Added See Also

  • 11 Jul 2013 11:08 AM

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

  • 7 Jul 2013 1:10 PM

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

  • 3 Jun 2013 12:46 PM

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

  • 3 Jun 2013 12:41 PM

    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!

  • 2 Jun 2013 4:10 PM

    Naomi  N edited Revision 4. Comment: Added tag

  • 2 Jun 2013 3:54 PM

    Naomi  N edited Revision 3. Comment: Adjusted link

  • 31 May 2013 12:15 PM

    Naomi  N edited Revision 2. Comment: Formatting

  • 31 May 2013 12:14 PM

    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.
Comments
  • Posted by
    on 11 Sep 2013 12:19 PM

    Naomi  N edited Revision 13. Comment: Added link

  • Posted by
    on 9 Sep 2013 1:05 PM

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

  • Posted by
    on 4 Sep 2013 2:44 PM

    Naomi  N edited Revision 10. Comment: Added See Also

  • Posted by
    on 11 Jul 2013 11:08 AM

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

  • Posted by
    on 7 Jul 2013 1:10 PM

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

  • Posted by
    on 12 Jun 2013 5:04 AM

    In SQL Server 2012 you can program as:

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

  • Posted by
    on 3 Jun 2013 12:46 PM

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

  • Posted by
    on 3 Jun 2013 12:41 PM

    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!

  • Posted by
    on 2 Jun 2013 4:10 PM

    Naomi  N edited Revision 4. Comment: Added tag

  • Posted by
    on 2 Jun 2013 3:54 PM

    Naomi  N edited Revision 3. Comment: Adjusted link

  • Posted by
    on 31 May 2013 12:15 PM

    Naomi  N edited Revision 2. Comment: Formatting

  • Posted by
    on 31 May 2013 12:14 PM

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

Page 1 of 1 (12 items)