convert numeric to varchar to datetime2

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  sql server   » convert numeric to varchar to datetime2

convert numeric to varchar to datetime2

Topic RSS Feed

Posts under the topic: convert numeric to varchar to datetime2

Posted: 7/13/2012

Padawan 189  points  Padawan
  • Joined on: 6/14/2010
  • Posts: 67
Hi forum

i have a date split into 3 columns with numeric data type
yy mm dd
1 10 22

i need to concatenate all 3 and finally load the date into datetime2 format

I'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_name

i get the result as 11022, i need to convert to datetime2 in the target



thanks in advance,

Posted: 7/13/2012

Jedi Knight 2272  points  Jedi Knight
  • Joined on: 2/22/2010
  • Posts: 201

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


Posted: 7/13/2012

Padawan 189  points  Padawan
  • Joined on: 6/14/2010
  • Posts: 67

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

Padawan 1346  points  Padawan
  • Joined on: 6/14/2012
  • Posts: 115
Answered  Answered

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

 

 

 

 

 

  

 


Page 1 of 1 (4 items)