New Year's Eve

Question

Post reply

New Year's Eve

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Revenant SSC-Forever Points: 42467 More actions · Answer 1

Revenant

SSC-Forever

Points: 42467

December 31, 2013 at 11:50 am

#1676654

Nice, very nice, Steve. Happy New Year to all!

TomThomson SSC Guru Points: 104773 More actions · Answer 2

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 SSC Guru Points: 95409 More actions · Answer 3

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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[/url]
Learn Extended Events

Revenant SSC-Forever Points: 42467 More actions · Answer 5

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Revenant (12/31/2013)
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.

+1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ken SSCoach Points: 17191 More actions · Answer 7

Nice question to end the year with. I have had to monkey around with dates this way before so it seemed quite familiar to me!

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 8

Nice and Easy..!!

But Why This (B) option: "select bdateadd( ss, -1, datediff(yy, 0, @ThisDate) + 1 )" Is it a Typing Mistake, or what.? 😉

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 9

Nice one, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Toreador SSChampion Points: 11360 More actions · Answer 10

Revenant (12/31/2013)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.

My SQL Server instance was installed with default settings, and has British date format. Presumably the default settings vary according to region?

It was obvious that US format was assumed though, as there was no option for 'error' or 'none of the above'.

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 11

PChiragS

SSCarpal Tunnel

Points: 4965

January 8, 2014 at 3:24 am

#1678360

nice question..

Thanks Steve.

jfgoude SSCrazy Points: 2586 More actions · Answer 12

jfgoude

SSCrazy

Points: 2586

January 31, 2014 at 6:31 am

#1685032

nice and easy

David Conn SSCertifiable Points: 5980 More actions · Answer 13

I'm disappointed that the question used the "datetime" data type. This has clearly been replaced by Date, Time and Datetime2. Obviously some of the established forum members can't move on.

Regards

David

JediSQL SSCrazy Points: 2117 More actions · Answer 14

I agree about moving forward with the newer temporal data types, but the old datetime has some nice features, like zero as an int will implicitly cast to 1900-01-01 00:00:00. The new ones don't do that. It's also frustrating that you can't do things like @MyDatetime2 = @MyDate + @MyTime; a glaring oversight if you ask me.

Sincerely,
Daniel