CODEWITHSUNDEEP
sql-servert-sqldatedatetime
LudvigH
LudvigH

Reputation: 4735

CONVERT vs CAST when exporting datetime as dates from sql server

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:

But I dont understand:

Upvotes: 0

Views: 2826

Answers (1)

TheGuyThatDoesn'tKnowMuch
TheGuyThatDoesn'tKnowMuch

Reputation: 1250

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

Upvotes: 1

Related Questions