Converting varchar into date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting varchar into date

Author

Topic

Sambhav
Starting Member

31 Posts

Posted - 2007-08-14 : 14:38:51

Hi
I have a table named prodwin in which i have to update a column name mfgdt which is varchar(10) to 7 months which I am able to do but the problem is that since it is only 10 character it is cutting the end of the year for eg

Before the date was
5/22/2000

UPDATE PRODWIN_MM SET MFGDT = DATEADD(MM,7,MFGDT)

Now it is this

Dec 23 200

Please tell me what to do , should i use cast or convert ?

Thanks in Adv

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 14:43:15

Increase the column length. Obviously its not the casting/converting/DateAdd thats causing the problem. The result string is getting truncated. so increase the column length and re-run your UPDATE.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-14 : 14:48:40

SET MFGDT = CONVERT(varchar(10),DATEADD(MM,7,MFGDT),101)

Jim

Sambhav
Starting Member

31 Posts

Posted - 2007-08-14 : 15:04:04

If I am using this

update prodwin_mm
SET MFGDT = CONVERT(varchar(10),DATEADD(MM,7,MFGDT),101)
I am getting this problem
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

and I cannot increase the varchar size :( ,
Initially my date in varchar field was this 5/22/2000 since i want to increase it by 7 months it should be 12/22/2000 which is exactly 10 characters .

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 15:08:07

What is your dateformat ? d-m-y?

Try this:


set dateformat mdy
go
select CONVERT(varchar(10),DATEADD(MM,7,'5/22/2000'),101)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Sambhav
Starting Member

31 Posts

Posted - 2007-08-14 : 15:08:41

update prodwin_mm
SET MFGDT = CONVERT(varchar(10),DATEADD(MM,7,MFGDT),101)

I tried Its working thanks a lot

Jim

Cheers!!!

sam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-16 : 02:04:00

Why is MFGDT a varchar datatype?

Madhivanan

Failing to plan is Planning to fail

Sambhav
Starting Member

31 Posts

Posted - 2007-08-16 : 10:04:37

Dude its like customers may type jan 2002 ...or may be just month and year like 12/1999 somthing like that ,even I was not happy with that varchar thing ...that to just 10 ...anyways my problem is solved for the time being thanks to SQL forum and Jim ofcourse

sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources