Hours Format - 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)

Hours Format

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2005-11-17 : 15:48:30

I have a dozen Excel files that I want to load into SQL and I can't figure out one of the Excel columns. The column in question is suppose to display an amount in hours. Ie. 84:08:00. Which it does, but when I click on the cell I see a date and time format. Ie. 1/3/1900 12:08:00 PM.

If I try to load the file into SQL it loads the data in the date and time format. I really want to see 84:08. Can anyone explain how I can convert the data, and how is Excel reading the data this way.

Many thanks.

GC

cbot
Starting Member

3 Posts

Posted - 2005-11-17 : 16:41:14

have you tried selecting the entire column in excel and changing the format to text?

alternately, you could try saving the excel file as a text file and then importing it.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-17 : 18:10:38

When it was converted from Excel to SQL Server is stored it as a time that is offset from 1899/12/31 00:00:00

This code shows how you can convert it into a varchar string in the format you want.


select
	[Excel Date] =	
	convert(varchar(20),datediff(hh,-1,'1/3/1900 12:08:00 PM'))+':'+
	right('00'+convert(varchar(20),datepart(minute,'1/3/1900 12:08:00 PM')),2)+':'+
	right('00'+convert(varchar(20),datepart(second,'1/3/1900 12:08:00 PM')),2)

Excel Date                 
-------------------------- 
84:08:00

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources