Convert 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 2000 Forums

Transact-SQL (2000)

Convert DateTime

Author

Topic

billsack
Starting Member

35 Posts

Posted - 2008-07-08 : 11:59:59

Hello experts,

I have a datefield that is in text format. It has yyyymmddhhmmss

ie 20080708165812

I want to get this into a datetime field but am struggling with the conversion. Can anyone help please.

Cheers

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-08 : 12:35:30

I don't know of a better way to do this, but it works:

declare @date varchar(50)
set @date = '20080708165812'

select CONVERT(datetime,LEFT(@date,8) + ' ' + SUBSTRING(@date,9,2) +':'+SUBSTRING(@date,11,2) +':'
+ RIGHT(@date,2))

Jim

Carl Forgey
Starting Member

6 Posts

Posted - 2008-07-08 : 12:53:39

Hey there,

I had a similar problem with a datetime as a float and came up with the following store proc to change it around. It would prolly be pretty easy to mod it to work with text strings...

CREATE FUNCTION DateFromFloat(@INPUT FLOAT)
RETURNS DATETIME
AS
BEGIN
DECLARE @RETURN DATETIME
SELECT @RETURN = CAST(LEFT(CAST(ROUND(@INPUT / 1000000, 0, 1) AS INT), 4) + '/' +
LEFT(RIGHT(CAST(ROUND(@INPUT / 1000000, 0, 1) AS INT), 4), 2) + '/' +
RIGHT(CAST(ROUND(@INPUT / 1000000, 0, 1) AS INT), 2) AS DATETIME)
RETURN @RETURN
END

billsack
Starting Member

35 Posts

Posted - 2008-07-09 : 05:18:17

Cheers Guys.

Have used Jims code. Thanks alot for that.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-09 : 10:14:59

Another method

select cast(stuff(stuff(stuff(@date,9,0,' '),12,0,':'),15,0,':') as datetime)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources