CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2012
user3115933
user3115933

Reputation: 4453

Using Dates in SQL Server 2012 Query

I'm using SQL Server 2012 and I need to write a query that will extract data greater than a particular date. The date field is called 'CreatedOn" and dates are recorded in this format "2014-08-18 17:02:57.903".

Currently, the date part of my query stands as follows:

    WHERE CreatedOn > '2014-08-18'

Problem is extracted data includes those of '2014-08-18'. It's like the > (greater than) is acting like >= (greater than or equal)!

How should I write my query if I need all data, say greater than '2014-08-18'?

Upvotes: 0

Views: 169

Answers (4)

Kritner
Kritner

Reputation: 13765

It sounds like when you're saying you want records "greater than '2014-08-18' you actually mean "records that occurred past 2014-08-18 23:59:59.999999" - you have to take into account time when working with dates, unless the time is otherwise removed (which in your sample data it was not.

You could do something like the following:

declare @gtDate datetime
set @gtDate = dateadd(d, 1, convert(datetime,convert(varchar(10), '2014-08-18', 101)))

....
WHERE CreatedOn >= @gtDate

Here we're taking your '2014-08-18', convert it to a varchar containing only the date (to help in case '2014-08-18' is ever '2014-08-18 12:00:00 as an example)

Then we convert the varchar back to a date, and add a day to it. In the end the statement says

Give me records that occured on 2014-08-19 or greater

EDIT:

Here's a fiddle demonstrating

http://sqlfiddle.com/#!6/90465/1

Note that we have 4 rows of data potential

insert into sampleData (Created)
select '2014-08-17'
union all select '2014-08-18'
union all select '2014-08-18 12:00:00'
union all select '2014-08-19'
union all select '2014-08-19 15:00:00'

only the bottom 2 rows (2014-08-19 and 2014-08-19 15:00:00) would be returned

Upvotes: 0

Elias
Elias

Reputation: 2632

As the others have said it is actually translating to CreatedOn > 2014-08-18 00:00:00 Instead try converting your datetime field to a short ate and compare those.

The 126 in Convert maps to the yyyy-mm-ddThh:mi:ss.mmm format.

WHERE CONVERT(char(10), CreatedOn,126) > '2014-08-18'

Upvotes: 0

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

Try the following condition. The problem is that 2014-08-18 is really 2014-08-18 00:00:00 (includes the hour), so any date time in that day will be greater.

WHERE CreatedOn >= '2014-08-19'

Upvotes: 1

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

'2014-08-18' actually means '2014-08-18 00:00:00'

So if you do not want 18th you should put either '2014-08-19' or specify the hours you want your date to be bigger of.

Upvotes: 0

Related Questions