convert char values 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

SQL Server 2005 Forums

Transact-SQL (2005)

convert char values into date

Author

Topic

ricky_vegas
Starting Member

3 Posts

Posted - 2008-01-24 : 12:50:31

I am working with 3rd party software that stores time in a SQL database field as char
For example: 33625 corresponds to 01/23/08.
I would like to convert this char value to datetime format (mm/dd/yy) at the time of query.
Any help would be greatly appreciated.
rick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-24 : 13:07:24

Use CONVERT().Look on BOL for syntax and usage.

zubamark
Starting Member

23 Posts

Posted - 2008-01-24 : 13:10:29

select CAST(33625 as datetime)

ricky_vegas
Starting Member

3 Posts

Posted - 2008-01-24 : 13:17:10

SELECT CAST(33625 AS DATETIME) is returning
1992-01-24 00:00:00.000

I was expecting 2008-01-24 or 2008-01-23

what's 33625?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-24 : 14:47:17

Is 33625 Days? If so, this should work:

SELECT DATEADD(DAY, 33625, '1916-01-01')

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-24 : 14:54:07

quote:
Originally posted by Lamprey

Is 33625 Days? If so, this should work:
SELECT DATEADD(DAY, 33625, '1916-01-01')

Maybe minutes since the begining of the current year.
SELECT DATEADD(Minute, -33625, GETDATE()) = 2008-01-01 05:26:57.967

If so then this will work: DATEADD(MINUTE, 33625, '20080101')

edit: Forgot you were looking for 01-23-2008 and not today. It may just be a coincidence that 01-01-2008 + 33625 minutes = 2008-01-24 08:25:00.000 unless it is minutes since the beginning of the last day of the previous year which would be absolutely ridiculous architecture.

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-24 : 14:59:28

you sure it's char and not int?

Man I hate it when they try to save space with hockey hacks

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ricky_vegas
Starting Member

3 Posts

Posted - 2008-01-24 : 17:17:27

Thanks everyone.

the SELECT DATEADD(DAY,33625,'1916-01-01') is giving me the results I needed.

I'm positive that all the fields are "char"

Thanks again for your precious help.

Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources