From DATETIME(OFFSET) to VARCHAR(x), a world of differences :)

Always use the smallest data type, but in some cases this can really bite you.
In my example, I’ve both DATETIME and DATETIMEOFFSET types in a single table.
‘Simply’ converting/casting both to strings and expecting similar results, isn’t an option.

Instead of the ‘Jan 2 2012 9:15AM’, it would be nice to just have it like ‘2012-01-02 09:15:48’
But…you’ll have to do that yourself 🙂




PRINT CAST('2012-01-02 09:15:48' AS datetime)
PRINT CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset)
PRINT CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)

SELECT '1| -- '+CAST(CAST('2012-01-02 09:15:48' AS datetime) AS VARCHAR(100)) AS Results
SELECT '2| -- '+CAST(CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset) AS VARCHAR(100))
SELECT '3| -- '+CAST(CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset) AS VARCHAR(100))
SELECT '4| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
SELECT '5| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
SELECT '6| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s