Posted: 7/13/2012
Hi forumi have a date split into 3 columns with numeric data typeyy mm dd1 10 22i need to concatenate all 3 and finally load the date into datetime2 formatI'm trying select ,cast(CONVERT(varchar(3),b.FYR,101)+CONVERT(varchar(3),b.FMO,101)+CONVERT(varchar(3),b.FDAY,101) as varchar(10)) from tbale_namei get the result as 11022, i need to convert to datetime2 in the target
thanks in advance,
Why are you converting them to strings? Have you looked at BOL examples for datetime2? http://msdn.microsoft.com/en-us/library/bb677335(v=sql.105).aspx
I needed to concatenate all the 3 numeric columns to make them a valid date. the time is not extracted from the columns can be 00
I tried this
,convert(datetime2,(RIGHT('00'+cast(FYR as varchar(2)),2))+(RIGHT('00'+cast(MO as varchar(2)),2))+(RIGHT('00'+cast(DaY as varchar(2)),2)) ) as test
Posted: 7/14/2012
You need to also add dashes or slashes. Here is an example where I am filling in the numbers for today's date:
CONVERT(DATETIME2,(RIGHT('0000'+cast(2012 as varchar(4)),4))+'-'+(RIGHT('00'+cast(7 as varchar(2)),2))+'-'+(RIGHT('00'+cast(14 as varchar(2)),2))) as test