Conversion of date time - 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)

Conversion of date time

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-10-10 : 10:27:41

Guys,

In my source data I have date time fields in weird format

MODDATE MODTIME
_______________________________
2006-09-29 00:00:00.000 1143
2000-06-27 00:00:00.000 1659

How can I convert the above values to 2006-09-29 11:43:00.000 and 2006-09-29 16:59:00.000

Any suggestions/inputs would help

Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-10 : 10:34:00

what datatype is the time stored as in the database?

declare @time varchar(20)
declare @date datetime
set @time = '1143'
set @date = '2006-09-29'

select @date + cast((left(@time,2)+':'+right(@time,2)) as datetime)

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-10 : 11:06:29

what is the datatype for MODTIME ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-11 : 01:58:33

If MODTIME is int, then

select dateadd(minute,@time/100*60+@time%100,@date)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources