Archives

Helpful Date Queries Which I Commonly Use

As I’ve mentioned before, I think I’m the most avid reader of my blog. I use it to document my work so that I don’t have to constantly re-invent the wheel. In this case, I find myself repeatedly searching for these scripts, often to populate SQL Reporting Services parameter defaults, so I thought I would share them with my readers as well.

-- Midnight of Current Day
SELECT dateadd (dd, 0, datediff (dd, 0, Getdate ()))
 
-- Midnight of last Friday (or whichever day).
/*The day of the week of date entered determines the result. Pick
a Friday, any Friday, and you will get last Friday.
Replace the - 0 with any increment of 7 to get previous weeks.*/
 
SELECT DATEADD (day,((DATEDIFF (day, '20120203', CURRENT_TIMESTAMP) / 7) * 7) - 0,'20120203')
 
--Day Number of Current year
-- Removed because the query was faulty. See comments. 
 
 
--First Day of Month
SELECT DATEADD (mm, DATEDIFF (mm, 0, GetDate ()), 0)
 
--Last Day of Month
 
SELECT DATEADD (s, -1, DATEADD (mm, DATEDIFF (m, 0, GETDATE ()) + 1, 0))
 
--First Day of Current Year
 
SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()), 0)
 
--First Day of Previous Years
--Substitute '-1' with whichever number of years to reverse
SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()) - 1, 0)
 
--First Day of Next Year
SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()) + 1, 0)
 
--Past 30 days.
SELECT DATEADD (dd, 0, DATEDIFF (dd, 30, GETDATE ()))
 
--Add in beginning and end of prior quarter.
SELECT DATEADD (qq, DATEDIFF (qq, 0, getdate ()), 0)
 
--First Day of Prior Quarter
SELECT DATEADD (qq, DATEDIFF (qq, 0, getdate ()) - 1, 0)

I’m not taking credit for these scripts as I’ve stolen borrowed these scripts from various places, most recently this excellent post.

Do you have any queries that you’d like to add?

Related posts:

8 comments to Helpful Date Queries I Commonly Use

  • Fred Crawford

    Thanks David, I never turn away a helpful tip!

  • Scott R.

    David,

    Thanks for your post on helpful date queries.

    In reviewing the query results in a test run, I found one query that does not give the desired result:

    –Day Number of Current year
    SELECT DATEDIFF (yy, 0, GetDate ())

    This query actually gives the number of years since 1900, and not the current day number of the current year. The 0 in the query returns the date 1900-01-01, the epoch date for the smalldatetime data type.

    Day number within the year combined with the year (YYYYDDD or YYYY-DDD) is sometimes (incorrectly) called a “Julian date” but is more accurately called an ordinal date (day number within the year – starting at 1 for January 1). See references to ISO 8601 (http://en.wikipedia.org/wiki/ISO_8601) for more information.

    Instead, try this query for day number within current year:

    –Day Number of Current year
    SELECT DATEDIFF(DD, CAST(YEAR(GETDATE()) AS varchar(4)) + ‘-01-01′, GETDATE()) + 1

    There may be even better ways to get the day number within the year. This is just one way, but it appears to give the desired result.

    Scott R.

  • Oops. You’re right that the query gives the wrong result. However, yours give a syntax error as well. :) I’m going to remove it until I have time to troubleshoot that query. Thanks!

  • Scott R.

    David,

    Thanks for your feedback.

    I copied and pasted my corrected query from your site, and also got a syntax error. The error is that single quotes copied from a web site get copied as separate open and close single quotes, instead of as non-specific single quotes that SQL is expecting. I have had this copy-paste issue with SQL scripts at other web sites, so I believe this is a common problem.

    The SQL I successfully tested and submitted in my reply had non-specific single quotes. The posting process of the reply likely changes the single quotes from non-specific to separate open and close single quotes – possibly outside of my control.

    After getting the syntax error on my query copied from your site, I corrected the quotes and the query ran successfully as expected. I successfully retested the query on SQL 2008 R2, SQL 2008, SQL 2005, and SQL 2000.

    I am guessing that quotes in SQL code in your main post get retained as non-specific (and run successfully after a copy / paste – I didn’t have any issues with my initial copy / paste of the SQL in your post), while quotes in SQL code in a reply may not get retained (and fail to run successfully after a copy / paste without correction). Just a guess.

    Scott R.

  • Joe Celko

    T-SQL now supports the ANSI CURRET]NT_TIMESTAMP, so you should not use the old Sybase/UNIX getdate(). With the new DATE data type we can rim off the time from a datetime with:

    CAST (CURRENT_TIMESTAMP AS DATE)

  • Allen

    I copied Scott’s code and had the single quote problem. I used metapad in an attempt to keep the single quotes.

    Hopefully everyone can successfully copy this:
    SELECT DATEDIFF(DD, CAST(YEAR(GETDATE()) AS varchar(4)) + ‘-01-01′, GETDATE()) + 1

    I stole borrowed this code.
    Thanks,
    Allen

  • Allen

    Nope this site flipped the quotes. One more try with different syntax in an attempt to learn. I won’t try again.

    SELECT DATEDIFF(DD, CAST(YEAR(GETDATE()) AS varchar(4)) + '-01-01', GETDATE()) + 1 -- usual paste into a < pre tag
    SELECT DATEDIFF(DD, CAST(YEAR(GETDATE()) AS varchar(4)) + \'-01-01\', GETDATE()) + 1 -- escaped with backslash in & pre tag

    SELECT DATEDIFF(DD, CAST(YEAR(GETDATE()) AS varchar(4)) + '-01-01', GETDATE()) + 1 — with & apos

    SELECT DATEDIFF(DD, CAST(YEAR(GETDATE()) AS varchar(4)) + \’-01-01\’, GETDATE()) + 1 — escaped with backslash

  • William Phillips

    My operative assumtion is “Day number of current year” is the number of days since the beginning of the year.. if so, DateDiff getdate against the First Day of Current Year query:

    SELECT NumDays=DATEDIFF(dd, DATEADD (yy, DATEDIFF (yy, 0, getdate ()), 0), GETDATE())

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>