Sql Server Convert Varchar to Datetime

sql

date-format

datetime-conversion

sql-server

byAnton Shumikhin·Oct 7, 2024

Convert a VARCHAR into a DATETIME with either CAST or CONVERT:

SELECT CAST('2021-08-25T13:14:15' AS DATETIME);

SELECT CONVERT(DATETIME, '2021-08-25T13:14:15', 126); -- Add ISO 8601 for better world peace

ISO 8601 format 'YYYY-MM-DDThh:mm:ss' will save you from the dreadful compatibility monster.

Decoding the date formats

Good news! Format codes are your new BFFs for accurately converting strings to dates:

SELECT CONVERT(DATETIME, '28-09-2011', 105); -- Italian renaissance date style DD-MM-YYYY
SELECT CONVERT(DATETIME, '18:25:00', 108); -- Time in all its glory HH:MI:SS

Format codes like 105 for 'dd-mm-yyyy' or 108 for 'hh:mi:ss' ensure SQL Server reads your mind right.

Stitching date and time

Having date and time as separated as a cat and water? Fuse them into a single DATETIME:

SELECT CONVERT(DATETIME, CONCAT(CONVERT(VARCHAR, dateColumn, 105), ' ', CONVERT(VARCHAR, timeColumn, 108))) AS TimeMashedPotato
FROM yourTable;

Turns out, VARCHAR components can cohabitate peacefully in a DATETIME.

Data integrity theatrics

Protect your data integrity from the lurking shadows by using RIGHT function and VARCHAR(10):

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), dateColumn, 105) + ' ' + RIGHT('0'+CONVERT(VARCHAR(2), hourColumn),2)  + ':' + RIGHT('0'+CONVERT(VARCHAR(2), minuteColumn),2) + ':00', 105) AS PrecisionIsKey
FROM yourTable;

Fascinating, isn't it? How accuracy and efficiency can save your data day.

Embracing TRY_CONVERT

When the going gets tough, TRY_CONVERT gets going:

SELECT TRY_CONVERT(DATETIME, yourVarcharColumn, 105) AS ComeWhatMay 
FROM yourTable;

If TRY_CONVERT trips, it gets up by returning NULL, so your SQL server does not throw tantrums.

War stories and peaceful outcomes

Battle with errors

When dealing with mutinous formats, the conversion process may turn rogue. Engage with conditional logic:

SELECT CASE 
         WHEN ISDATE(yourVarcharColumn) = 1 THEN 
            CONVERT(DATETIME, yourVarcharColumn) 
         ELSE 
            NULL 
       END AS NoMoreBadDates 
FROM yourTable;

No more mutiny on the bounty, only valid dates sail through, preventing a mutiny of runtime exceptions.

Localizing the battlefield

Alert! The locale of your data needs your attention. Squash regional differences:

SET LANGUAGE French;
SELECT CONVERT(DATETIME, '25/08/2021', 103); -- When in France, format as the French do

Teach SQL Server a bit of language and it follows the right date format standards.

Advanced tactics

For tactical manipulation of dates and times, deploy functions like DATEADD, DATEDIFF, and DATEPART:

-- What's the time +1 hour from now? Let's ask SQL!
SELECT DATEADD(hour, 1, GETDATE()); 

-- Detective SQL, find the difference between these two dates
SELECT DATEDIFF(day, '2021-08-25', '2021-08-30'); 

-- SQL, do your thing. Show us the magic month part
SELECT DATEPART(month, '2021-08-25');