Extract Time from Numeric field

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)

Extract Time from Numeric field

Author

Topic

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2010-04-07 : 08:29:23

I have a data source that gives me a date time field in numeric form (EG 57996700.0) Now by using the following code I can convert it to a date time but my date is the datestamp that is on the code. (DATEADD(n, CAST(Start + 60 AS INT) % 1440, DATEADD(d, CAST(Start + 60 AS INT) / 1440, '1900-01-01' )) AS StartTime) as you can see I have tried applying the start of time but to no success.

Can any one give me guidance

Whitmoj
If I have inspired one person today then my job is done.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-07 : 08:47:43

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2010-04-07 : 09:10:19

2010-04-07 00:00:00.000 57996700.0 57997260.0 2010-04-09 12:40:00.000 2010-04-09 22:00:00.000
2010-04-07 00:00:00.000 57996660.0 57997200.0 2010-04-09 12:00:00.000 2010-04-09 21:00:00.000
2010-04-07 00:00:00.000 57996480.0 57997020.0 2010-04-09 09:00:00.000 2010-04-09 18:00:00.000
2010-04-07 00:00:00.000 57996420.0 57996780.0 2010-04-09 08:00:00.000 2010-04-09 14:00:00.000
2010-04-07 00:00:00.000 57996540.0 57997140.0 2010-04-09 10:00:00.000 2010-04-09 20:00:00.000
2010-04-07 00:00:00.000 57996420.0 57996900.0 2010-04-09 08:00:00.000 2010-04-09 16:00:00.000
2010-04-07 00:00:00.000 57996420.0 57996980.0 2010-04-09 08:00:00.000 2010-04-09 17:20:00.000
2010-04-07 00:00:00.000 57996480.0 57997020.0 2010-04-09 09:00:00.000 2010-04-09 18:00:00.000
2010-04-07 00:00:00.000 57996465.0 57996960.0 2010-04-09 08:45:00.000 2010-04-09 17:00:00.000
2010-04-07 00:00:00.000 57996510.0 57996750.0 2010-04-09 09:30:00.000 2010-04-09 13:30:00.000
2010-04-07 00:00:00.000 57996420.0 57996930.0 2010-04-09 08:00:00.000 2010-04-09 16:30:00.000
2010-04-07 00:00:00.000 57996510.0 57996810.0 2010-04-09 09:30:00.000 2010-04-09 14:30:00.000
2010-04-07 00:00:00.000 57996765.0 57997260.0 2010-04-09 13:45:00.000 2010-04-09 22:00:00.000
2010-04-07 00:00:00.000 57996510.0 57997020.0 2010-04-09 09:30:00.000 2010-04-09 18:00:00.000
2010-04-07 00:00:00.000 57996480.0 57996720.0 2010-04-09 09:00:00.000 2010-04-09 13:00:00.000
2010-04-07 00:00:00.000 57996420.0 57997050.0 2010-04-09 08:00:00.000 2010-04-09 18:30:00.000
2010-04-07 00:00:00.000 57997020.0 57997260.0 2010-04-09 18:00:00.000 2010-04-09 22:00:00.000
2010-04-07 00:00:00.000 57996480.0 57996720.0 2010-04-09 09:00:00.000 2010-04-09 13:00:00.000
2010-04-07 00:00:00.000 57996600.0 57997110.0 2010-04-09 11:00:00.000 2010-04-09 19:30:00.000
2010-04-07 00:00:00.000 57996570.0 57997050.0 2010-04-09 10:30:00.000 2010-04-09 18:30:00.000
2010-04-07 00:00:00.000 57996510.0 57997020.0 2010-04-09 09:30:00.000 2010-04-09 18:00:00.000
2010-04-07 00:00:00.000 57996600.0 57997140.0 2010-04-09 11:00:00.000 2010-04-09 20:00:00.000
2010-04-07 00:00:00.000 57996510.0 57996765.0 2010-04-09 09:30:00.000 2010-04-09 13:45:00.000
2010-04-07 00:00:00.000 57996420.0 57996900.0 2010-04-09 08:00:00.000 2010-04-09 16:00:00.000

What I am after is where the date reads 2010-04-09 08:00:00.000 I need it to read 1900-01-01 08:00:00.000

Whitmoj
If I have inspired one person today then my job is done.

Sachin.Nand

2937 Posts

Posted - 2010-04-07 : 09:26:41

select convert(datetime,convert(varchar(30),datecolumn,114))from yourtable

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-07 : 09:37:08

select dateadd(day,datediff(day,0,datecolumn),0) from yourtable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources