SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 convert varchar to datetime		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jung1975
Aged Yak Warrior

USA
503 Posts
Posted - 06/07/2005 :  17:56:20  Show Profile  Reply with Quote
How can I convert varchar to datetime?.

i.e 'Apr 05' to '20050431'

nosepicker
Constraint Violating Yak Guru

USA
366 Posts
Posted - 06/07/2005 :  18:04:29  Show Profile  Reply with Quote
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

United Kingdom
583 Posts
Posted - 06/07/2005 :  18:22:16  Show Profile  Reply with Quote
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

USA
503 Posts
Posted - 06/07/2005 :  18:32:11  Show Profile  Reply with Quote
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)

USA
6997 Posts
Posted - 06/07/2005 :  18:37:48  Show Profile  Reply with Quote
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

United Kingdom
583 Posts
Posted - 06/08/2005 :  01:39:17  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000