Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Finding New Years Eve Expand / Collapse
Author
Message
Posted Friday, January 4, 2013 9:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
Or, since Nigel has led us outside the box....
Select convert(datetime, rtrim(Datepart(year,getdate())-1)+'-12-31')

Yeah, I cheat by using rtrim() as a quickly coded forced conversion to a string.
Post #1402965
Posted Friday, January 4, 2013 11:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 1,285, Visits: 1,637
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
Post #1403033
Posted Monday, January 7, 2013 4:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 2:33 AM
Points: 1,178, Visits: 2,644
sknox (1/4/2013)
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.
...


Very good point. How about this one:

SELECT DATEADD(year,DATEDIFF(YEAR,'19001231',GETDATE())-1,'19001231')



--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1403526
Posted Wednesday, January 9, 2013 7:38 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 3, 2014 7:28 PM
Points: 58, Visits: 212
Whilst others are being picky I thought I would add my bit.

None of the answers are right

When is New Years Eve anyway?

If its January 9th (as it is today) then New years eve this year will be 31-12-2013.

31-12-2012 was new years eve LAST YEAR

Very picky I know but I think technically correct

Dave




David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
Post #1404789
Posted Wednesday, January 9, 2013 7:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:42 AM
Points: 1,704, Visits: 6,213
DavidBridgeTechnology.com (1/9/2013)
When is New Years Eve anyway?



February 9th
Post #1404792
Posted Wednesday, January 9, 2013 9:26 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 3, 2014 7:28 PM
Points: 58, Visits: 212
Toreador (1/9/2013)
DavidBridgeTechnology.com (1/9/2013)
When is New Years Eve anyway?



February 9th



Ha Ha.




David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
Post #1404858
Posted Wednesday, January 9, 2013 9:34 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 3, 2014 7:28 PM
Points: 58, Visits: 212
-- In 2008 + you can use a date instead of a datetime with implicit conversion

DECLARE @Date date = GETDATE()

SELECT DATEADD(DAY, -DATEPART(dayofyear,@Date),@Date)




David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
Post #1404859
Posted Wednesday, January 9, 2013 9:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 2:33 AM
Points: 1,178, Visits: 2,644
DavidBridgeTechnology.com (1/9/2013)
Whilst others are being picky I thought I would add my bit.

None of the answers are right

When is New Years Eve anyway?

If its January 9th (as it is today) then New years eve this year will be 31-12-2013.

31-12-2012 was new years eve LAST YEAR

Very picky I know but I think technically correct

Dave


Correct, the original question didn't explicitly state whether it should return New Years Eve for this year or last, but the correct answer implied that it should be last years (2012/12/31)


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1404873
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse