Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Steve Jones Finding New Years Eve RE: Finding New Years Eve

  • January 4, 2013 at 11:32 am

    #1573887

    nigel. (1/4/2013)

    Thanks Steve for the question.

    Thought I'd throw this in just for fun. Should work for every day of the year:

    SELECT DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE())

    The only problem with that is, since getdate() returns a datetime[2]*, you're actually returning some time during the day on New Years Eve rather than the day of New Years Eve itself.

    Of course, as of SQL2008R2, this still works:

    SELECT cast(cast(GETDATE() as int)-DATEPART(dayofyear,getdate())-1 as datetime)

    If you have SQL 2008 or higher and want to avoid questionable conversions, you could just modify your code thus:

    SELECT CAST(DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE()) as DATE)

    *depending on your SQL Server version