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