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 2005 Forums

Transact-SQL (2005)

Convert varchar to datetime

Author

Topic

esilva002
Starting Member

10 Posts

Posted - 2009-08-10 : 12:42:21

I have a table that has a column called COCDAT which has contains dates in vachar(8) mmddyyyy format. So the data looks like 11012009 as an example. The clients want data to be stored that way. But, I have an access log that I want to do date comparison. I need to convert varchar(8) to datetime, 101 to compare dates. I pull the data into a view to convert but I always get an 'out of range datetime value'. Please Help!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-10 : 12:52:02

You can use something like


select convert(datetime,stuff(STUFF ( COCDAT, 3, 0 ,'/' ),6,0,'/'),101)
from mytable

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

esilva002
Starting Member

10 Posts

Posted - 2009-08-10 : 13:19:30

Thank you works perfectly! and now I have learned about the Stuff function.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-08-10 : 15:11:48

[code]select
*,
[DateTime] =
convert(datetime,right(DT,4)+left(DT,4))
from
( -- Test Data
select DT = '02282008'
) a[/code]
Results:
[code]DT DateTime
-------- -----------------------
02282008 2008-02-28 00:00:00.000

(1 row(s) affected)[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources