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).
Go to Top of Page

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
Go to Top of Page

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


Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -