Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Steve Jones » Finding New Years Eve
Add to briefcase
48 posts, Page 5 of 5
«««12345

Finding New Years Eve Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
john.arnott
Posted Friday, January 04, 2013 9:38 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491, Visits: 3,010
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
sknox
Posted Friday, January 04, 2013 11:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:32 AM
Points: 1,059, Visits: 1,396
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
nigel.
Posted Monday, January 07, 2013 4:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 03, 2013 9:46 AM
Points: 1,164, Visits: 2,603
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
DavidBridgeTechnology.com
Posted Wednesday, January 09, 2013 7:38 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 58, Visits: 189
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
Toreador
Posted Wednesday, January 09, 2013 7:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 1,384, Visits: 4,884
DavidBridgeTechnology.com (1/9/2013)
When is New Years Eve anyway?



February 9th
Post #1404792
DavidBridgeTechnology.com
Posted Wednesday, January 09, 2013 9:26 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 58, Visits: 189
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
DavidBridgeTechnology.com
Posted Wednesday, January 09, 2013 9:34 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 58, Visits: 189
-- 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
nigel.
Posted Wednesday, January 09, 2013 9:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 03, 2013 9:46 AM
Points: 1,164, Visits: 2,603
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
48 posts, Page 5 of 5
«««12345

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.