Datetime field to intger time 1

[simian101]

I have a regular date time field like

2008-08-20 15:05:24.000

I need to convert it to a integer time like

150,524

Any thoughts?

Thanks

Simi

 

[jbenson001]

What does the integer represent? Minutes, seconds....?

 

[simian101]

15:05:24.000
150,524

15 is the hour
05 minutes
24 seconds...

it is a weird as400 format.

Thanks

Simi

 

[jbenson001]

This is ugly, but works, not sure if there is a better way:

SELECT
CAST(LEFT(RIGHT(CONVERT(varchar(50),createddtime, 21), 12),2) + 
SUBSTRING(RIGHT(CONVERT(varchar(50),createddtime, 21), 12),4,2) +
SUBSTRING(RIGHT(CONVERT(varchar(50),createddtime, 21), 12),7,2) AS integer) AS TimeAsInt
FROM YourTable

 

[simian101]

That appears to work.

Thanks

Simi

 

[gmmastros]

I would have suggested you use the DatePart function.


IntegerValue = Hours * 10000 + Minutes * 100 + Seconds

[tt][blue]Select DatePart(Hour,GetDate()) * 10000 + DatePart(Minute, GetDate()) * 100 + DatePart(Second, GetDate())[/blue][/tt]

Alternatively, using style 108 gets you most of the way there. Just replace ':' with empty string '' and convert to integer. Like this...

[tt][blue]Select Convert(Int, Replace(Convert(VarChar(100), GetDate(), 108), ':', ''))[/blue][/tt]

If you are running this on a large dataset, you will probably want to test this several different ways to see which gives the best performance.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

See, I knew there was a better way... LOL