SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


New Year's Eve


New Year's Eve

Author
Message
Carlo Romagnano
Carlo Romagnano
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4880 Visits: 3326
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
:-D

I run on tuttopodismo
steve.jacobs
steve.jacobs
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 Visits: 279
Steve, thanks for the question and Happy New Year to all.
JediSQL
JediSQL
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 189
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
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14240 Visits: 12197
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! Hehe

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

Tom

Aaron N. Cutshall
Aaron N. Cutshall
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1863 Visits: 1033
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
Revenant
Revenant
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7312 Visits: 4859
Nice, very nice, Steve. Happy New Year to all!
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14240 Visits: 12197
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

WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9769 Visits: 10569
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
Author - SQL Server T-SQL Recipes
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

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32096 Visits: 18551
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. :-D:-D;-)



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Revenant
Revenant
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7312 Visits: 4859
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. :-D:-D;-)

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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search