CODEWITHSUNDEEP
sqlsql-server
Joeysonic
Joeysonic

Reputation: 265

Varchar date to datetime sql

I have a varchar date format of 01012018, I need to convert this to a date in a SQL Server view:

CONVERT(VARCHAR(50), CONVERT(DATE, [Date], 103), 103)

I have tried the above with no joy.

Please help

Upvotes: 0

Views: 107

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

SQL Server can be a bit cumbersome when parsing dates that are not one of the "built-in" ones. You can use parse()/try_parse(), but that is a relatively new function.

In your case, it is pretty simple to construct the date value directly:

select convert(date, concat(right(ddmmyyyy, 4), substring(ddmmyyyy, 3, 2), left(ddmmyyyy, 2)))
from (values ('01012018')) v(ddmmyyyy)

Upvotes: 4

Ilyes
Ilyes

Reputation: 14926

Use CONVERT() as

DECLARE @Var VARCHAR(10) = '01012018'

SELECT CONVERT(DATE,
               CONCAT( RIGHT(@Var, 4),
                       SUBSTRING(@Var, 3, 2),
                       LEFT(@Var, 2)
                     )
              )

Demo

Finally, I would recommend ALTERing your table (which is the right solution) and change the data type of your column to DATE data type.

CREATE TABLE T(
  MyCol VARCHAR(10)
);

INSERT INTO T(MyCol) VALUES
('01012018'),
('01022018'),
('01032018'),
('WrongData');

SELECT MyCol
FROM T;

UPDATE T
SET MyCol = TRY_CONVERT(DATE,
                        CONCAT( RIGHT(MyCol, 4),
                                SUBSTRING(MyCol, 3, 2),
                                LEFT(MyCol, 2)
                              )
                        );
ALTER TABLE T
ALTER COLUMN MyCol DATE;

SELECT MyCol
FROM T;

Second Demo

Upvotes: 1

Thom A
Thom A

Reputation: 96028

There is no style ddMMyyyy in the CONVERT Date and Time Styles, but there is a dd/MM/yyyy, so you could inject these characters in and convert:

SELECT TRY_CONVERT(date, STUFF(STUFF(v.ddmmyyyy, 5, 0, '/'), 3, 0, '/'), 103)
FROM (VALUES ('01012018')) v (ddmmyyyy);

I've also used TRY_CONVERT incase you have some other "bad" values (perhaps '01312019'). These will return NULL when the varchar represents an invalid date.

Upvotes: 2

Related Questions