How to convert date YYYYYMM as YYYYMMDD using sqlserver?
Hi friends, I have a requirement like I have to convert YYYYMM to YYYYMMDD and then change that column name accordingly to othername.But that column is having char as datatype. I tried several ways to convert char to datetime.It is successfull but I am not able to convert YYYYMM to YYYYMMDD. Can anyone help on this please? Regards, Sudahsudha
June 28th, 2012 2:00am

Do you want to do it in t-sql? Follow this example. You have to concatenate YYYYMM with DD (ex: 01). And then convert it to datetime. declare @ds as nvarchar(50) set @ds='201206' declare @dd as datetime set @dd=cast(@ds+'01' as datetime) select @dd
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2012 2:42am

In a derived column the expression would look like this: (DT_DATE)(SUBSTRING(myCharDateColumn,1,4) + "-" + SUBSTRING(myCharDateColumn,5,2) + "-" + "01") MCTS, MCITP - Please mark posts as answered where appropriate.
June 28th, 2012 3:03am

Hi, I have used above derived column expression but of no use. Can some one guide this in sqlserver. Regards, Sudhasudha
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2012 4:02am

Did you get an error?MCTS, MCITP - Please mark posts as answered where appropriate.
June 28th, 2012 4:09am

yes.I got an error by using the expression.Here is the expression which I am using; (DT_DATE)(SUBSTRING([ColumnName],1,4) + "-" + SUBSTRING([ColumnName],5,2) +"-"+"01" sudha
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2012 4:45am

For starters: there's a bracket missing at the end. Secondly, my crystal ball is in the shop for repair, so it would be very very useful if you'd post the actual error.MCTS, MCITP - Please mark posts as answered where appropriate.
June 28th, 2012 4:52am

Hi, Please find the error I am getting; sudha In the words of Neo from the Matrix: There is no spoon.MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2012 5:16am

Hi , Now that expression is working fine,but when I am converting the particular column into datetime,it is prompting error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. sudha
June 28th, 2012 5:31am

I'm sorry, it should be DT_DBDATE instead of DT_DATE. Try that one and let know how it goes.MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2012 5:36am

Thanks for your answers.Expression is working with DT_DATE.But I have aproblem while converting char column to datetime column. Regards, Sudhasudha
June 28th, 2012 5:50am

Does it work with DT_DATE or DTDB_DATE? There's an important difference between those two. If it works, how come you still have a problem converting char column to datetime? And again, what's the error?MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2012 5:54am

When I am using the query Select cast(column name as datetime)as columnname1 from tablename. I am getting error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. sudha
June 28th, 2012 6:41am

Probably because there are string values that do not fall into the rang of available dates: January 1, 1753, through December 31, 9999.MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2012 6:46am

Probably because there are string values that do not fall into the rang of available dates: January 1, 1753, through December 31, 9999.MCTS, MCITP - Please mark posts as answered where appropriate.
June 28th, 2012 6:46am

I have got my issue solved. Thanks alot for your help. Regards, Sudhasudha
Free Windows Admin Tool Kit Click here and download it now
June 28th, 2012 7:42am

Hi, By using below ways we can change YYYYMM to YYYYMMDD and also we can change datatype from char to datetime. This can be done in two ways using sqlserver and SSIS. In Sqlserver: select convert(datetime,columnname + '01',112) from table In SSIS: (DT_DBDATE)(SUBSTRING([ColumnName],1,4) + "-" + SUBSTRING([ColumnName],5,2) +"-"+"01"sudha
July 4th, 2012 5:21am

Hi, By using below ways we can change YYYYMM to YYYYMMDD and also we can change datatype from char to datetime. This can be done in two ways using sqlserver and SSIS. In Sqlserver: select convert(datetime,columnname + '01',112) from table In SSIS: (DT_DBDATE)(SUBSTRING([ColumnName],1,4) + "-" + SUBSTRING([ColumnName],5,2) +"-"+"01"sudha
Free Windows Admin Tool Kit Click here and download it now
July 4th, 2012 5:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics