Delete statement suggestion that will help me fix my statement

Question

Delete statement suggestion that will help me fix my statement

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Michael Meierruth (4/30/2015)
Eric M Russell (4/30/2015)
Michael Meierruth (4/30/2015)
For easier readability, I always write my hard code dates as 'yyyy-mm-dd'.
99999999 is always assumed by SQL and most programming languages to be in ISO standard YYYYMMDD format. But 2015-05-12 could be interpreted as YYYY-MM-DD or YYYY-DD-MM depending on the RDMS, application software, or localization settings.
As far as I know, the format YYYY-MM-DD is always interpreted as such independent of the localization setting.

Give the following a spin in SSMS:

set dateformat mdy;

select cast('2015-04-30' as datetime);

set dateformat dmy;

select cast('2015-04-30' as datetime);

set dateformat mdy;

select cast('20150430' as datetime);

set dateformat dmy;

select cast('20150430' as datetime);

set dateformat mdy;

Michael Meierruth SSChampion Points: 10051 More actions · Answer 2

Pretty absurd this 'set dateformat' stuff.

But I'll now vote for 'yyyymmdd' when hard coding dates.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

To prevent a conversion error, I had to change 2015-04-30 to 2015-04-10, where the month and day can be interchangable.

set dateformat mdy;

select cast('2015-04-10' as datetime);

2015-04-10 00:00:00.000

set dateformat dmy;

select cast('2015-04-10' as datetime);

2015-10-04 00:00:00.000

set dateformat mdy;

select cast('20150410' as datetime);

2015-04-10 00:00:00.000

set dateformat dmy;

select cast('20150410' as datetime);

2015-04-10 00:00:00.000

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Eric M Russell (4/30/2015)
To prevent a conversion error, I had to change 2015-04-30 to 2015-04-10, where the month and day can be interchangable.
set dateformat mdy;
select cast('2015-04-10' as datetime);
2015-04-10 00:00:00.000
set dateformat dmy;
select cast('2015-04-10' as datetime);
2015-10-04 00:00:00.000
set dateformat mdy;
select cast('20150410' as datetime);
2015-04-10 00:00:00.000
set dateformat dmy;
select cast('20150410' as datetime);
2015-04-10 00:00:00.000

You seem to be missing the point. I purposely used 2015-04-30 in the code to demonstrate that you can't depend on the 'YYYY-MM-DD' format to always work when converting string dates to date/time data types.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Michael Meierruth (4/30/2015)
Pretty absurd this 'set dateformat' stuff.
But I'll now vote for 'yyyymmdd' when hard coding dates.

Not really when you consider that this setting can be affected by how the server is setup, such as using UK - English.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Just to be clear. There are 2 datetime formats that won't be affected by SET DATEFORMAT.

One for dates only: 'yyyymmdd'

And one for datetime values: 'yyyy-mm-ddThh:mi:ss.sss'

Both comply with ISO 8601 formats for dates. Adding hyphens for the first format or removing them or the T in the second format will cause unexpected results or errors.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 7

Just to be clear, the entire world would be way better off if everybody, always used, spoke and thought in YYYYMMDD. Is it the 1st of April or April 1st? AHG!! All due respect to my Canadian friends.

Don Simpson

I'm not sure about Heisenberg.

dogramone Hall of Fame Points: 3216 More actions · Answer 8

My favorite date format for hard coded dates is dd mmm yyyy, so 15 apr 2015. I can't see any valid date format set on the server or db fail to interpret that.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

dogramone (5/1/2015)
My favorite date format for hard coded dates is dd mmm yyyy, so 15 apr 2015. I can't see any valid date format set on the server or db fail to interpret that.

really?

set language french;

select cast('15 apr 2015' as date)

Le paramètre de langue est passé à Français.
Msg 241, Level 16, State 1, Line 3
Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

dogramone Hall of Fame Points: 3216 More actions · Answer 10

OK Fair call. So make sure MMM is equal to the abbreviation for the language installed, but it works regardless of the dateformat set.