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 ««123»»

New Year's Eve Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 9:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 2,451, Visits: 2,342
sqlnaive (12/30/2013)
Easy QOTD to end the year on good note.

Thanks Steve and all for making the year wonderful and interactive with QOTD efforts. And a wish you a wonderful year ahead.
Happy New Year

+1
Post #1526782
Posted Tuesday, December 31, 2013 9:48 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 20, 2014 6:00 AM
Points: 588, Visits: 276
Steve, thanks for the question and Happy New Year to all.
Post #1526791
Posted Tuesday, December 31, 2013 10:22 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 7:04 PM
Points: 44, Visits: 132
That is a nice trick that
dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0)

returns the first moment of next year. However, it does have the downside that if @ThisDate is one of the newer date&time data types (i.e., datetime2, datetimeoffset), the result is converted back to the old datetime data type.

This is because DATEDIFF just returns an integer, losing all concept of the data type of @ThisDate, and then DATEADD, with only integers as arguments, defaults to the datetime datatype.

If the result is cast to datetime2 (either explicitly, or implicitly by assignment to a variable or column of the data type), the loss of microseconds and nanoseconds doesn't really matter since the idea is to zero out the fractional part anyway. However, with datetimeoffset one may need to retain the timezone offset:
declare
@ThisDate datetimeoffset(7) = SYSDATETIMEOFFSET()
;
select
[Input] = @ThisDate
, [LoseTZ] = CAST(DATEADD( ss, -1, DATEADD(yy, DATEDIFF(yy, 0, @ThisDate) + 1, 0) ) AS datetimeoffset(7))
, [WithTZ] = TODATETIMEOFFSET(DATEADD( ss, -1, DATEADD(yy, DATEDIFF(yy, 0, @ThisDate) + 1, 0) ), DATEPART(tz, @ThisDate))
;



Sincerely,
Daniel
Post #1526802
Posted Tuesday, December 31, 2013 10:39 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:00 AM
Points: 8,551, Visits: 9,043
Aaron N. Cutshall (12/31/2013)
It's interesting to point out that for the correct solution the starting date did not have to be Dec 31 but could have been ANY date within 2013.

Pretty slick there, Steve!

The last second of 2013? How can that be on any day but Dec 31?


Tom
Post #1526808
Posted Tuesday, December 31, 2013 10:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 1,090, Visits: 541
L' Eomot Inversé (12/31/2013)
The last second of 2013? How can that be on any day but Dec 31?

I meant that the date in the variable could have been any day of the year since only the year portion was utilized. Of course, the last second of the year will be on Dec 31!



"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
Post #1526810
Posted Tuesday, December 31, 2013 11:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 4,383, Visits: 3,393
Nice, very nice, Steve. Happy New Year to all!
Post #1526823
Posted Tuesday, December 31, 2013 12:25 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:00 AM
Points: 8,551, Visits: 9,043
Aaron N. Cutshall (12/31/2013)
L' Eomot Inversé (12/31/2013)
The last second of 2013? How can that be on any day but Dec 31?

I meant that the date in the variable could have been any day of the year since only the year portion was utilized. Of course, the last second of the year will be on Dec 31!

Aargh! I should have read your coment more carefully.


Tom
Post #1526833
Posted Tuesday, December 31, 2013 12:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 PM
Points: 6,582, Visits: 8,860
Since the QotD is all about learning, allow me to point out a few things:

First of all, none of these solutions really work...
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@ThisDate".
(when run on a case-sensitive collation, so change them all to be as defined: @thisdate)

And our European friends, using the YDM format, will get this:
Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.


To have this code work right, just use the ISO 8601 standard for dates/times:
select @thisdate = '2013-12-31T00:00:00' -- dates with times - the milliseconds are optional
--or
select @thisdate = '20131231' -- just dates


The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.

Ref: datetime


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1526835
Posted Tuesday, December 31, 2013 4:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 21,209, Visits: 14,899
WayneS (12/31/2013)
Since the QotD is all about learning, allow me to point out a few things:

First of all, none of these solutions really work...
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@ThisDate".
(when run on a case-sensitive collation, so change them all to be as defined: @thisdate)

And our European friends, using the YDM format, will get this:
Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.


To have this code work right, just use the ISO 8601 standard for dates/times:
select @thisdate = '2013-12-31T00:00:00' -- dates with times - the milliseconds are optional
--or
select @thisdate = '20131231' -- just dates


The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.

Ref: datetime


You could also just fix the code as follows:
SET DATEFORMAT 'YMD' 

declare @ThisDate SMALLDATETIME = '2013/12/31'

And to clarify a bit, it's the Instance collation and not just the database collation that will cause those issues Wayne mentioned.
It should also be noted that Wayne fixed the second option otherwise his error message would have been different.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1526865
Posted Tuesday, December 31, 2013 7:13 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 4,383, Visits: 3,393
SQLRNNR (12/31/2013)
WayneS (12/31/2013)
Since the QotD is all about learning, allow me to point out a few things:

First of all, none of these solutions really work...
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@ThisDate".
(when run on a case-sensitive collation, so change them all to be as defined: @thisdate)

And our European friends, using the YDM format, will get this:
Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.


To have this code work right, just use the ISO 8601 standard for dates/times:
select @thisdate = '2013-12-31T00:00:00' -- dates with times - the milliseconds are optional
--or
select @thisdate = '20131231' -- just dates


The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.

Ref: datetime


You could also just fix the code as follows:
SET DATEFORMAT 'YMD' 

declare @ThisDate SMALLDATETIME = '2013/12/31'

And to clarify a bit, it's the Instance collation and not just the database collation that will cause those issues Wayne mentioned.
It should also be noted that Wayne fixed the second option otherwise his error message would have been different.

I thought it was understood that QotDs assume default SQL Server installation settings - if we veered off into region-specific settings, QotDs would have to have a dozen lines of code before you would get to the gist of the question.
Post #1526871
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse