date time conversion - 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

General SQL Server Forums

New to SQL Server Programming

date time conversion

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2009-12-15 : 14:32:17

hi all,
i am pulling some data that gives me the date and hour in the following format ( yyyymmddhhEST) i.e 2009110100EST/2009110101EST/2009110102EST/2009110103EST
i want to split it and convert it to two format that give me the date yyyymmdd and the hour hh i.e 20091101 00 /20091101 01 / 20091101 02...

thanks for the help

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-15 : 15:00:08

I'm guessing that you are using SQL 2k8?

If not, then why not just use datetime?

In any case

SELECT CONVERT(datetime, SUBSTRING(Col, 1, 11))

????

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

korssane
Posting Yak Master

104 Posts

Posted - 2009-12-15 : 15:07:03

hi thanks for the quick reply.
no i am using SQL 2K5 .
this data format (yyyymmddhhEST) is pulled from another source of data...i xcan not change it from the source.

thanks

korssane
Posting Yak Master

104 Posts

Posted - 2009-12-15 : 15:18:23

thanks BRETT ,
it works .
Do you know how can i separate the date part from the hh part ?

thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-15 : 15:33:44

I think you are storing the dates in a varchar field???

You will just have to deal with them as strings in that case...like..

declare @d varchar(20)
set @d = '2009110101EST'
SELECT SUBSTRING(@d, 1, 8), SUBSTRING(@d, 9, 2)

Change them to valid datetime fields soon.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-15 : 15:34:46

If you are just trying to separate the values you can do this. But this is not of a datetime format, so you can't do math on it
declare @inString varchar(20)
set @inString = '2009110103EST'

select
substring(@inString,1,8) + ' '+
substring(@inString,9,2)
,DATEADD(hh,convert(int,substring(@inString,9,2)),convert(datetime,substring(@inString,1,8)))

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-15 : 16:01:20

quote:
Originally posted by korssane

hi thanks for the quick reply.

that's what my wife sa....ummm never mind

quote:

thanks BRETT ,
it works.

Same thing

You need to check out ISDATE()

[/quote]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 01:23:24

<<
You need to check out ISDATE()
>>

ISDATE() is not fully reliable
You need to check the length too

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

korssane
Posting Yak Master

104 Posts

Posted - 2009-12-16 : 08:34:27

thank you all for your help.
i really appreciated it..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources