Convert YYYYMMDD to DATE

Question

I have a bunch of dates in varchar like this   20080107 20090101 20100405       How do I convert them to a date format like this   2008-01-07 2009-01-01 2010-04-05   I ve tried using this   SELECT  FIRST NAME          MIDDLE NAME          LAST NAME               cast  GRADUATION DATE  as date          FROM mydb   But get this message      Msg 241  Level 16  State 1  Line 2Conversion failed when converting date and or time from character string

User · Answer

Use SELECT CONVERT date   20140327    In your case   SELECT  FIRST NAME           MIDDLE NAME           LAST NAME          CONVERT date   GRADUATION DATE        FROM mydb

User · Answer

The error is happening because you  or whoever designed this table  have a bunch of dates in VARCHAR  Why are you  or whoever designed this table  storing dates as strings  Do you  or whoever designed this table  also store salary and prices and distances as strings   To find the values that are causing issues  so you  or whoever designed this table  can fix them    SELECT GRADUATION DATE FROM mydb   WHERE ISDATE GRADUATION DATE    0    Bet you have at least one row  Fix those values  and then FIX THE TABLE  Or ask whoever designed the table to FIX THE TABLE  Really nicely   ALTER TABLE mydb ALTER COLUMN GRADUATION DATE DATE    Now you don t have to worry about the formatting - you can always format as YYYYMMDD or YYYY-MM-DD on the client  or using CONVERT in SQL  When you have a valid date as a string literal  you can use   SELECT CONVERT CHAR 10    20120101   120        but this is better done on the client  if at all    There s a popular term - garbage in  garbage out  You re never going to be able to convert to a date  never mind convert to a string in a specific format  if your data type choice  or the data type choice of whoever designed the table  inherently allows garbage into your table  Please fix it  Or ask whoever designed the table  again  really nicely  to fix it

User · Answer

I was also facing the same issue where I was receiving the Transaction Date as YYYYMMDD in bigint format  So I converted it into Datetime format using below query and saved it in new column with datetime format  I hope this will help you as well  SELECT convert  Datetime  STUFF STUFF Transaction Date  5  0   -    8  0   -    120  As  Transaction Date New  FROM mydb

User · Answer

In your case it should be   Select convert datetime convert varchar 10  GRADUATION DATE 120   as  GRADUATION DATE  from mydb

[sql] Convert YYYYMMDD to DATE

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008-r2