convert varchar to datetime - 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

SQL Server 2000 Forums

Transact-SQL (2000)

convert varchar to datetime

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-06-07 : 17:56:20

How can I convert varchar to datetime?.

i.e 'Apr 05' to '20050431'

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-07 : 18:04:29

Do you only want the last day of the month, because there is no date in 'Apr 05' (plus, there are only 30 days in April).

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-07 : 18:22:16

Try


SELECT DATEADD(mm,1,DATEADD(dd,-1,(CONVERT(datetime,REPLACE('01-' + 'Apr 05', ' ', '-')))))

Sorry cant get to the 31st though

Beauty is in the eyes of the beerholder

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-06-07 : 18:32:11

Sorry, Yeas I want the last day of month. i.e.'20050430'

Actually, there is a column called [month] which has varchar(10) as the dat type.
It has the data looks like:


Month
----
Apr 05
May 05
Jun 05
..

I am trying to convert this column to datetime bu tkeep getting an error:
Syntax error converting datetime from character string.

SELECT DATEADD(mm,1,DATEADD(dd,-1,(CONVERT(datetime,[month]))))from callstat

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-07 : 18:37:48

I think you need to reverse the order that you do the dateadd DD and dateadd MM.

See what happens when the month is March:


select ADD_DD_FIRST =
dateadd(mm,1,dateadd(dd,-1,(convert(datetime,replace('01-' + 'Mar 05', ' ', '-')))))

select ADD_MM_FIRST =
DATEADD(dd,-1,dateadd(mm,1,convert(datetime,'01-'+'Mar 05')))

ADD_DD_FIRST                                           
------------------------------------------------------ 
2005-03-28 00:00:00.000

(1 row(s) affected)

ADD_MM_FIRST                                           
------------------------------------------------------ 
2005-03-31 00:00:00.000

(1 row(s) affected)

quote:
Originally posted by AndyB13

Try

SELECT DATEADD(mm,1,DATEADD(dd,-1,(CONVERT(datetime,REPLACE('01-' + 'Apr 05', ' ', '-')))))

Sorry cant get to the 31st though

Beauty is in the eyes of the beerholder

CODO ERGO SUM

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-08 : 01:39:17

Thanks Michael, yes your right.
I didnt spot that as i didnt try it on any other months

Cheers

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources