Geek Answers Handbook

How to check time on datetime fields, but ignore date?

I have a column that stores data in a date and time format. I want to check all instances where the temporary part of this column is not 00: 00: 00: 000 - the date does not matter.

Basically, if the time function () was a function, something like this:

SELECT * FROM progen.DY WHERE TIME(DY_DATE) <> '00:00:00:000'

How can I do it?

+7
source share
5 answers

You only need a little customization of what you already have.

 SELECT * FROM progen.DY WHERE TIME(DY_DATE) <> '00:00:00:000'

Use CONVERT to change DATETIME to TIME .

 SELECT * FROM progen.DY WHERE CONVERT(TIME, DY_DATE) <> '00:00:00:000'
+9
source

Use DATEDIFF and DATEADD to get datetime datetime instead. Compare the column only with the date, and it will return those rows that have non-zero time.

How it works, we first calculate the difference (in days) between the era and the value. We add this number in the era to create a new datetime. Since the result of DATEDIFF is an integer, any time component is rounded.

 SELECT * FROM Table WHERE DateColumn <> DATEADD(d, DATEDIFF(d, 0, DateColumn), 0)

Then the time function can be implemented as follows, but I do not recommend it for this particular scenario:

 SELECT DATEDIFF(minute, DATEADD(d, DATEDIFF(d, 0, DateColumn), 0), DateColumn) as MinutesIntoDay, -- or, if you require higher precision DATEDIFF(second, DATEADD(d, DATEDIFF(d, 0, DateColumn), 0), DateColumn) as MinutesIntoDay FROM Table

Edit: As mentioned in other answers, you can use DATE to achieve the same effect as DATEADD(d, DATEDIFF(d, 0, DateColumn), 0) , which clears well. However, DATE is only added in SQL Server 2008, while the formula is compatible with at least SQL 2000. Therefore, if you need backward compatibility or deal with SQL CE, casting in DATE is not available.

+3
source

Another way is to convert it to another data type, for example

 SELECT * FROM progen.DY WHERE CAST(DY_DATE as float) - CAST(DY_DATE as int) > 0
+3
source 
 SELECT * FROM progen.DY WHERE CONVERT(TIME, DY_DATE - CONVERT(DATE, DY_DATE)) > '00:00'
+2
source

I do this all the time, trying to see if the table column should turn into a date instead of a date and time, which is really the answer.

 select * from progen.dy where cast(dy_date as Date) <> dy_date

The listing removes time, and datetime has a higher priority, so when comparing, if they are not equal, then it has a time value. The same thing can be done with a cast to time, with a little syntax.

+2
source

More articles:


All Articles