CODEWITHSUNDEEP
sql-serverdatetime
Pedro Estevão
Pedro Estevão

Reputation: 1074

Convert varchar to datetime on SQL Server with custom format

I have a varchar column that represents a date in the following format: "DDMMYYHHMMSS". I'm converting this varchar to datetime in this way:

select * from myTable where getdate() >= convert(datetime, concat(case when substring(myDate, 5, 2) >= '0' and substring(myDate, 5, 2) <= '50' then 20 else 19 end, substring(myDate, 5, 2), '-', substring(myDate, 3, 2), '-', substring(myDate, 1, 2), ' ', substring(myDate, 7, 2), ':', substring(myDate, 9, 2), ':', substring(myDate, 11, 2)), 120)

Explaining: first I'm breaking the varchar to get all the fields separately (day, month, year, etc). Then I'm concatenating these fields to build a varchar in a know format for SQL Server: "YYYY-MM-DD HH:MI:SS". Finally, I'm using convert function to convert the varchar to datetime with format 120. There is a conditional at the beginning of SQL where I check the 2-digit year to know how to convert it to 4-digit year.

I want to know if there is a better way to write this SQL. (it has to work with SQL Server 2005 and later versions)

In Oracle it seems to much easier to me:

sysdate >= to_date(myDate, 'DDMMYYHH24MISS')

Upvotes: 0

Views: 1941

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

If you are on SQL Server 2012 or later you can use DATETIMEFROMPARTS (Transact-SQL)

declare @S varchar(12) = '100598101112';

select datetimefromparts(
                        iif(substring(@S, 5, 2) <= 50, '20', '19') + substring(@S, 5, 2),
                        substring(@S, 3, 2),
                        substring(@S, 1, 2),
                        substring(@S, 7, 2),
                        substring(@S, 9, 2),
                        substring(@S, 11, 2),
                        0
                        );

Upvotes: 5

Related Questions