0

I have been able to find a lot of information for getting a string representation of just the time from a datetime column like this one.

I need to get the time part out of a datetime in a way that I can do some math on it like adding it to another datetime. So a string representation of the time wont help me.

However I've only found one example that will extract the time as a numeric type value. I.e:

SELECT CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))

This method requires two casts though and I have to run this on over 10,000 rows. Is there anything similar to the dateadd method for extracting the date part from a datetime column i.e.: 

select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

that I can use to get just the time out of a datetime column and return it as a decimal or datetime? Perhaps a solution that uses less casting?

I am using SQL Server 2000.

Community
  • 1
  • 1
egerardus
  • 11,316
  • 12
  • 80
  • 123
  • What is the purpose for your conversion? – Robert Aug 17 '12 at 21:03
  • I have to add a time value for an event to a date-only value so that I can compare it with another table as part of a join. The time value for the type of an event I have to extract from another table that holds both dates and times for an event type. – egerardus Aug 17 '12 at 21:16
  • When you say "numeric value" please note that this would have to be a count of milliseconds, minutes, or other time value, *not* float or decimal, because those don't work! Try `SELECT Convert(datetime, Convert(decimal(38, 31), Convert(datetime, '2012-08-17 00:00:00.003')))`. This yields `2012-08-17 00:00:00.000`. Oops. `float` has the same problem. – ErikE Aug 17 '12 at 22:10

2 Answers2

1

One way You can get the time in seconds is with:

select cast(datediff(second, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0), getdate())/(60*60*24.0) as datetime)

This calculates the time in seconds and then converts back to a datetime.

To get it as a decimal:

select datediff(second, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0), getdate())/(60*60*24.0)

Or use "ms" if you prefer millisecond precision.

Or, you can use the more readable:

select datepart(hh, getdate())/24.0+datepart(mm, getdate())/(24*60.0)+
       datepart(ss, getdate())/(24*60*60.0)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That looks workable, any idea if it processes any faster than two casts with all the additional math? I am not very savvy with SQL processing speeds. – egerardus Aug 17 '12 at 21:04
  • For just 10,000 records, I wouldn't worry about it. If you were dealing with 50 casts per row and millions of rows, then you might start to nice the performance. The casts are possibly faster. – Gordon Linoff Aug 17 '12 at 21:06
1

To get a datetime:

SELECT GetDate() - DateDiff(day, 0, GetDate());
-- returns the time with zero as the datetime part (1900-01-01).

And to get a number representing the time:

SELECT DateDiff(millisecond, DateDiff(day, 0, GetDate()), GetDate());
-- time since midnight in milliseconds, use as you wish

If you really want a string, then:

SELECT Convert(varchar(8), GetDate(), 108); -- 'hh:mm:ss'
SELECT Convert(varchar(12), GetDate(), 114); -- 'hh:mm:ss.nnn' where nnn is milliseconds
ErikE
  • 48,881
  • 23
  • 151
  • 196
  • That first one seems like the simplest. – egerardus Aug 17 '12 at 21:22
  • Needed decimal or datetime because I have to do some more processing in the SQL with the result. The first one does the trick. – egerardus Aug 17 '12 at 21:45
  • Don't use `decimal`!!! It won't work! Try `SELECT Convert(datetime, Convert(decimal(38, 31), Convert(datetime, '2012-08-17 00:00:00.003')))`. This yields `2012-08-17 00:00:00.000`. Oops. `float` has the same problem. – ErikE Aug 17 '12 at 22:09
  • Well the first one still works because it's a datetime right? I assume I can add the result of that to another datetime safely? – egerardus Aug 18 '12 at 01:10