CODEWITHSUNDEEP
sqlsql-serverdate
d2907
d2907

Reputation: 902

Getting the right date in sql

I'm executing the next query in sql server 2012.

select *
from table
where date > convert(date, '2015/02/12')
order by date asc

but I'm getting the next set:

2015-02-12 06:40:42.000
2015-02-12 06:45:44.000
2015-02-12 06:48:15.000
2015-02-12 07:06:28.000
2015-02-12 07:26:46.000 
...

I can fix this by changing the date to '2015/02/13', but I have the doubt about this behavior, why am I getting dates from feb 12 when I am specifying that I need only later dates ?. I also tried using cast('2015/02/12' as date), but I could not have the answer I was looking for

Upvotes: 0

Views: 563

Answers (4)

Paul Rowe
Paul Rowe

Reputation: 788

jarlh is right, though I'll clarify a little. Each of the "dates" you show above fall after 12:00 midnight starting 2015-02-12. They are actually timestamps.

If you don't want to see anything for the day specified in the filter, you add a day and use the greater-than-or-equal-to (>=) operator.

SELECT *
FROM table
WHERE (date >= DATEADD(d, 1, CONVERT(date, '2015/02/12')))
ORDER BY date ASC

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

When comparing the date 2015/02/12 with your datetime data, this will implicitly compare the converted date 2015-02-12 00:00:000, the date at the beginning of the day with all of your data in column date.

But you are actually comparing datetime data, which has a time part as well, which gets compared.

And because you're comparing the beginning of the day (2015-02-12 00:00:000) with a value which is after it, for example 2015-02-12 06:40:42, all of the dates from will be displayed, because 6:40 AM is after (greater than) 0:00 AM.

Try this:

SELECT *
FROM TABLE
WHERE DATE >= DATEADD(SECOND, -1, '2015/02/13')

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95512

why am I getting dates from feb 12 when I am specifying that I need only later dates ?

You're not specifying that you need only dates after Feb 12. You're asking for every row in which the value in the "date" column is greater than '2015-02-12'.

The value '2015-02-12 06:40:42.000' is greater than '2015-02-12'.

Upvotes: 1

Bill Gregg
Bill Gregg

Reputation: 7147

Because dates without times are intepreted as 12:00 midnight on that date. If you want only dates after February 12, 2015, then select all dates greater than or equal to February 13, 2015 (which again will be interpreted as midnight on February 13th).

select *
from table
where date >= convert(date, '2015/02/13')
order by date asc

Upvotes: 1

Related Questions