How to convert from varchar to datetime datatype [Resolved]

Posted by Kirthiga under Sql Server on 7/30/2012 | Points: 10 | Views : 2625 | Status : [Member] | Replies : 12
Hi,

While running this qurey,

DECLARE @Month varchar(2),@Year varchar(4)
SET @Month = '02' SET @Year = '2012'
select convert(datetime,@Year+@Month+cast(DAY(Duedate) as varchar(2))) from sample


Im getting error
Conversion failed when converting date and/or time from character string.

How to solve this error




Responses

Posted by: Kirthiga on: 7/31/2012 [Member] Starter | Points: 25

Up
0
Down

Resolved
Hi All,

Thanks for your reply, I got my answer by own

DECLARE @Month varchar(2),@Year varchar(4)

SET @Month = '02' SET @Year = '2012'
select convert(datetime,@Year+'-'+@Month+'-'+cast(DAY(Duedate) as varchar(2))) from sample


Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 7/30/2012 [Member] Starter | Points: 25

Up
0
Down
try this

DECLARE @Month varchar(2),@Year varchar(4)

SET @Month = '02'
SET @Year = '2012'
SELECT CONVERT(DATETIME,@Month+@Year+DAY(DUEDATE))

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 7/30/2012 [Member] Silver | Points: 25
Posted by: Kirthiga on: 7/30/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Oswaldlily,

Thank you for your reply

I got my values in datetime datatype but data is not correct

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 7/30/2012 [Member] Starter | Points: 25

Up
0
Down
@KIRTHIGA
wHAT IS YOUR EXPECTED DATA

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 7/30/2012 [Member] Silver | Points: 25

Up
0
Down
2.http://forums.asp.net/t/1342191.aspx/1

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 7/30/2012 [Member] Starter | Points: 25

Up
0
Down
DECLARE @Month varchar(2),@Year varchar(4)

SET @Month = '02'
SET @Year = '2012'
select convert(varchar(10),@Month+@Year+DAY(DUEDATE),120)

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 7/30/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Oswaldlily,


DUEDATE Expected Output
2003-07-20 00:00:00.000 2012-02-20 00:00:00.000 1960-04-28 00:00:00.000


Output came like this

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 7/30/2012 [Member] Starter | Points: 25

Up
0
Down
DECLARE @Month varchar(2),@Year varchar(4)

SET @Month = '02'
SET @Year = '2012'

select @Year+'-'+@Month+'-'+cast(DAY('2003-07-20')as varchar(2))

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muktesh.Chavle on: 7/30/2012 [Member] Starter | Points: 25

Up
0
Down
Try below -

declare @year varchar(4)
set @year = '2012-'
declare @mnth varchar(2)
set @mnth = '-02-'

select @year + @mnth + convert(varchar,day(DUEDATE))

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jasminej on: 7/31/2012 [Member] Starter | Points: 25

Up
0
Down
@Kirthiga

Could you pls "mark as answer" if u got the answer! (or) kindly reply what you want exactly!

So, Experts don't want to get into this area and wasting their time!

Thanks for the understanding!

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 7/31/2012 [Member] Silver | Points: 25

Up
0
Down
That right...Please mark it answer,that saves lot of time...-:)

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response