0

I help out a collegue with exporting data from SQL Server 2012. I type manual queries and then copy/paste the results to an Excel sheet that I then share with him. The extract I make will be an excel file that will be manually analysed. No need for automation.

What I try to do is settling for best practice when exporting order statistics grouped by day, trying to learn SQL better. I have a field of type datetime, and I want to convert this to some date format. In my locale, the typical date format is YYYY-MM-DD. I do sometimes share my script with others, that might have another locale. I have found three statements that seem to yield the same values for me. 

select top 1 
createdat
, CAST(createdat as date)          as A
, CONVERT(char(10), createdat,126) as B
, CONVERT(char(10), createdat,127) as C
from dbo.[Order]

resulting in 

createdat               |A          |B          |C
2012-12-27 08:23:32.397 |2012-12-27 |2012-12-27 |2012-12-27

From the TSQL MSDN reference (link) I understand that:

  • A is handled by SQL as type Date, whereas B and C are chars.
  • B and C should differ by their time zone handling.

But I dont understand:

  • HOW does B and C handle time zones?
  • What is the practical difference when copy/pasting to Excel?
  • Is there practical difference if I share this script with collegues using another locale I should consider?
  • Should one or the other be preferred?
TheGuyThatDoesn'tKnowMuch
  • 1,240
  • 1
  • 17
  • 28
LudvigH
  • 3,662
  • 5
  • 31
  • 49
  • 2
    A is the better option. [`Date` data type have no display format](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028), only strings that represent dates have display formats. therefor, you will be better of simply using `cast(datetime as date)`. However, I'm not sure how excel would handle dates. – Zohar Peled Mar 24 '16 at 12:31
  • my personal opinion, understanding excel file date formats is un-imaginable. Better to write the SQL in excel itself to import data from SQL Server. Adding more, copy-paste doesn't always works correctly in this case ... – Aditya Mar 24 '16 at 12:55

1 Answers1

1

To answer your questions sequentially:

  1. 126 uses the ISO 8601 date standard, which signifies the Year aspect to be the full 4 characters long, rather than just the last two. 127 uses the same date standard, but in Time Zone Zulu, which is a military time zone (4 hours ahead of EST)

  2. There essentially is no difference when copy/pasting to Excel. When opening an Excel doc, the default cell formatting is "General". When you paste any of these date types into Excel, it will register option A as a number (in this case 41270) and based on the pre-existing format from your query will convert it to Date format. Options B and C will first register as text, but since they are in the format of a Date (i.e. they have the "/" marks), Excel can register these as dates as well and change the formatting accordingly.

  3. As long as the person you are sharing your script with uses T-SQL this shouldn't cause problems. MySQL or other variations could start to cause issues.

  4. CAST(createdat as date) is the best option (IMO)

Sources:

SQL Conversion Types

ISO 8601 Details

Zulu Time Zone

TheGuyThatDoesn'tKnowMuch
  • 1,240
  • 1
  • 17
  • 28
  • re:1) my time stamps are given in datetime format. I know that the server local time is UTC+01:00 right now. I dont know if it observes DST. Should i excpect conversion codes 126 and 127 to give the same date in the end, or will they differ if the time stamp is close to midnight? – LudvigH Mar 24 '16 at 14:08
  • I actually tested that and the dates still come out the same. It doesn't convert the original time based on an assumed time zone. It instead just assigns a time zone and assumes your time is already in that zone. My test was changing the time on your original field from `2012-12-27 08:23:32.397` to `2012-12-27 23:23:32.397`, and both 126 and 127 still returned `2012-12-27` – TheGuyThatDoesn'tKnowMuch Mar 24 '16 at 14:40
  • Thank you! Accepting answer now! :) – LudvigH Mar 24 '16 at 16:59