Plane Plane - 6 months ago 11
SQL Question

DATE TIME Conversions

I am very new to SQL, well not so new any longer.

I'm just curious... I just would like to know why I have seen inconsistencies in

Date
conversions.

Why do some developers use:
SELECT CONVERT(CHAR(10), GETDATE(), 101)
== > this makes sense to me:
05/24/1987 10 chars


Some use:

SELECT CONVERT(CHAR(12), GETDATE(), 101)


Can someone break this down? Are we just being careful, maybe, with leading/trailing spaces?

And I have even seen:

SELECT CONVERT(CHAR(15), GETDATE(), 101)


Why anyone would use this?

Answer

If you want the data as a string to be as slim as possible, one could use this date to string command:

CONVERT(CHAR(10), GETDATE(), 20)

I don't see any good reason for using 101 instead of 20 at the end there.

If you want the data as slim as possible and it doesn't matter if the date is still a date (and not a string), one could use this command:

convert(date, GETDATE())

Ultimately, the end result is the pretty much the same for each -- a unique value per date.

I guess some developers might include trailing spaces for some personal expression or something -- or, like, doing weird things to more easily track down what code they wrote, maybe.


SELECT 
CONVERT(CHAR(10), GETDATE(), 101) as Datestamp_CHAR10_101
, CONVERT(CHAR(10), GETDATE(), 20) as Datestamp_CHAR20_20 -- imho, the best way to store a date as a string
, CONVERT(CHAR(12), GETDATE(), 101) as Datestamp_CHAR12_101
, CONVERT(CHAR(15), GETDATE(), 101) as Datestamp_CHAR15_101
, CONVERT(CHAR(23), GETDATE(), 121)  as Datestamp_CHAR25_131
, DATALENGTH(CONVERT(CHAR(10), GETDATE(), 101)) as DATALENGTH_CHAR10_101 -- 10
, DATALENGTH(CONVERT(CHAR(10), GETDATE(), 20)) as DATALENGTH_CHAR20_20 -- 10
, DATALENGTH(CONVERT(CHAR(12), GETDATE(), 101)) as DATALENGTH_CHAR12_101 -- 12
, DATALENGTH(CONVERT(CHAR(15), GETDATE(), 101)) as DATALENGTH_CHAR15_101 -- 15
, DATALENGTH(CONVERT(CHAR(23), GETDATE(), 121))  as DATALENGTH_CHAR25_131 -- 23
, GETDATE()
, convert(date, GETDATE()) -- Probably the best answer. Why change the data to a string ..?
, DATALENGTH(GETDATE() ) -- 8
, DATALENGTH(convert(date, GETDATE()) ) -- 3
Comments