Is it possible to convert string column to datetime in sql server?

Question

I have a table CUSTOMERS (ID,NAME,REGISTRATION_DT). It has following values -

1,Alex,093013
2,Peter,012512
3,Mary,041911
. . .

I want to be able to select REGISTRATION_DT column and have the values retun as DATETIME. Is there any query to convert the existing varchar datatype in REGISTRATION_DT column to datetime and have all values in datetime format? The existing varchar values are in ddmmyy format.

I am using sql-server 2008

Assuming this is an ETL issue, you might want to write a function that takes the date string and an epoch year (for instance, if your epoch year is 1970, any two digit year after 69 would be in the 20th century, but any two digit year before 70 would be in the 21st century) and returns a date. If it isn't ETL, you should change the data type of the column to DATETIME or SMALLDATETIME and fix the application code that writes to that column. — outis nihil
– outis nihil, Commented Mar 27, 2014 at 17:45

Luke Franklin · Accepted Answer · 2014-03-27 17:37:46Z

1

The below example requires the year to always be in 2000. you will also need to change the string YourTable to the table you want to query from.

SELECT
      Id
     ,Name
     --Convert To Date Time
     ,CONVERT(datetime,
                -- Add 20 to the final 2 numbers to represent the year i.e 2013
                ('20' + SUBSTRING(REGISTRATION_DT,5,2)
                    + '-' + 
                -- the first 2 numbers to represent the Month i.e 09
                SUBSTRING(REGISTRATION_DT,1,2)
                    + '-' + 
                -- the middle 2 numbers to represent the day i.e 30
                SUBSTRING(REGISTRATION_DT,3,2))) 
     as REGISTRATION_DT
from YourTable

answered Mar 27, 2014 at 17:37

Luke Franklin

3551 silver badge11 bronze badges

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

1 Comment

Reeya Oberoi Over a year ago

Thanks @Luke Franklin for answering the question and explaining the logic in comments. Thanks a ton!

user2989408 · Accepted Answer · 2014-03-27 17:33:15Z

0

The only way is to convert the varchar to date. Try something like

SELECT CONVERT(DATE, LEFT(REGISTRATION_DT, 2) + '/'  
                       + LEFT(RIGHT(REGISTRATION_DT, 4), 2)
                       + '/20' + RIGHT(REGISTRATION_DT, 2))

An example SQL FIDDLE here.

More importantly though, you must consider changing the way you save the REGISTRATION_DT field in your table.

answered Mar 27, 2014 at 17:33

user2989408

3,1371 gold badge20 silver badges15 bronze badges

Comments

Aryan · Accepted Answer · 2014-03-27 17:45:38Z

0

Try This...

select ID, NAME ,CAST((substring(REGISTRATION_DT,5,6)+
substring(REGISTRATION_DT,1,4)) as datetime) 
as Registration_Date  from CUSTOMERS

As example here: http://sqlfiddle.com/#!3/d41d8/32154/0

edited Mar 27, 2014 at 17:45

answered Mar 27, 2014 at 17:40

Aryan

1041 silver badge7 bronze badges

Collectives™ on Stack Overflow

Is it possible to convert string column to datetime in sql server?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related