0

I have a column that pulls in two dates, 1 of the dates has the correct date and time so I can easily compare the number of minutes between this date and GetDate() but some values have the date 01/01/1900 and then the time i need to use.

How can I do datediff but ignore the date and only use the times?

So

My date   | 01/01/1900 14:25:00
GetDate() | 06/02/2014 14:26:00

Would give me 1 minute

user3219693
  • 201
  • 3
  • 20

1 Answers1

3

Convert your datetime to the time data type and then everything works as expected.

Ex:

Declare @d1 DateTime,
        @d2 DateTime

Select  @d1 = '01/01/1900 14:25:00',
        @d2 = '06/02/2014 14:26:00'

Select DATEDIFF(minute, Convert(Time, @d1), Convert(Time, @d2))

The time data type was added in SQL2008 (which you have tagged in your question).

George Mastros
  • 24,112
  • 4
  • 51
  • 59
  • This seems like the right answer, but it makes the assumption that if the date is always supposed to be today. Op said that about the `19000101`, but didn't specify about the rest of the dates – Lamak Feb 06 '14 at 14:59
  • The table will always populate with the 19000101 date so I will mark this solution as the answer. – user3219693 Feb 06 '14 at 15:00
  • This answers the final line in the question, "How can I do datediff but ignore the date and only use the times?". If there is more to it, I would expect the OP to say so. – George Mastros Feb 06 '14 at 15:00
  • @user3219693 Then this is the right answer. Though if you don't ever need the date part, then you should use a `TIME` column instead of a `DATETIME` one – Lamak Feb 06 '14 at 15:04