Delete statement suggestion that will help me fix my statement

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  April 30, 2015 at 8:05 am

  #1794685

  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

  April 30, 2015 at 8:31 am

  #1794702

  Pretty absurd this 'set dateformat' stuff.

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

• Eric M Russell

  SSC Guru

  Points: 125255

  More actions

  April 30, 2015 at 8:33 am

  #1794705

  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: 442447

  More actions

  April 30, 2015 at 8:38 am

  #1794710

  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: 442447

  More actions

  April 30, 2015 at 8:40 am

  #1794711

  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: 183686

  More actions

  April 30, 2015 at 9:04 am

  #1794727

  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

  May 1, 2015 at 4:56 pm

  #1795148

  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

  May 1, 2015 at 5:38 pm

  #1795152

  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: 47068

  More actions

  May 1, 2015 at 5:54 pm

  #1795153

  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);

• 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]