Convert varchar to datetime on SQL Server with custom format

Question

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')

Mikael Eriksson · Accepted Answer · 2015-02-11 12:29:48Z

5

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
                        );

answered Feb 11, 2015 at 12:29

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pedro Estevão Over a year ago

Thanks. But I forgot to mention I have to maintain compatibility with older versions of SQL Server up to 2005.

Collectives™ on Stack Overflow

Convert varchar to datetime on SQL Server with custom format

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related