issue with datetime

Question

issue with datetime

spectra

SSCarpal Tunnel

Points: 4120
More actions
September 26, 2014 at 6:42 am

#296836

I have a table which has a column CreatedOn . .. datatype is datetime.
This where clause below does not filter data properly. What I am doing wrong ?
where CONVERT(VARCHAR(10),CreatedOn,103) between '01/08/2014' and '26/09/2014'

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23008 More actions · Answer 1

What exactly is the problem? You need to be very careful using BETWEEN with datetime. You're much better using >= and <=.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 2

BWFC (9/26/2014)
What exactly is the problem? You need to be very careful using BETWEEN with datetime. You're much better using >= and <=.

Column has a value 2014-09-24 14:49:35.190 . It did not match

Neil Burton SSC-Insane Points: 23008 More actions · Answer 3

I'm struggling to understand what you need. Is that the only row you need returning? Can you post some sample data please.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

BWFC (9/26/2014)
What exactly is the problem? You need to be very careful using BETWEEN with datetime. You're much better using >= and <=.

Looks ok to me:

-- Non-SARGable

;WITH MyTable AS (

SELECT CreatedOn = CAST('20140731' AS DATETIME) UNION ALL

SELECT CAST('20140801' AS DATETIME) UNION ALL

SELECT CAST('20140802' AS DATETIME) UNION ALL

SELECT CAST('2014-09-24 14:49:35.190 ' AS DATETIME) UNION ALL

SELECT CAST('20140925' AS DATETIME) UNION ALL

SELECT CAST('20140926' AS DATETIME) UNION ALL

SELECT CAST('20140927' AS DATETIME)

)

SELECT *

FROM MyTable

WHERE CONVERT(VARCHAR(10),CreatedOn,103) BETWEEN '01/08/2014' AND '26/09/2014'

-- SARGable

;WITH MyTable AS (

SELECT CreatedOn = CAST('20140731' AS DATETIME) UNION ALL

SELECT CAST('20140801' AS DATETIME) UNION ALL

SELECT CAST('20140802' AS DATETIME) UNION ALL

SELECT CAST('2014-09-24 14:49:35.190 ' AS DATETIME) UNION ALL

SELECT CAST('20140925' AS DATETIME) UNION ALL

SELECT CAST('20140926' AS DATETIME) UNION ALL

SELECT CAST('20140927' AS DATETIME)

)

SELECT *

FROM MyTable

WHERE CAST(CreatedOn AS DATE) BETWEEN CAST('20140801' AS DATE) AND CAST('20140926' AS DATE)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Drop the CONVERT.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 6

BWFC (9/26/2014)
I'm struggling to understand what you need. Is that the only row you need returning? Can you post some sample data please.

I have these records ..

sample data:

------------------

2014-09-26 14:25:51.310

2014-09-26 12:46:42.647

2014-09-24 14:49:35.190

2014-09-24 12:39:21.150

2014-09-19 15:22:36.233

2014-09-19 12:48:07.910

2014-09-18 18:55:58.070

2014-09-18 18:47:12.960

I expect all these records to output because they belong to the date range specified in the where clause.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

But your where clause is not checking for a date range. It's checking for a string range. All three values are varchar, so you're getting string comparisons. Remove the CONVERT and you'll be doing the date range check you want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Neil Burton SSC-Insane Points: 23008 More actions · Answer 8

create table #Test

(

CreatedOn datetime

,ID int identity(1,1)

)

insert into #test

select '2014-09-26 14:25:51.310' union all

select '2014-09-26 12:46:42.647' union all

select '2014-09-24 14:49:35.190' union all

select '2014-09-24 12:39:21.150' union all

select '2014-09-19 15:22:36.233' union all

select '2014-09-19 12:48:07.910' union all

select '2014-09-18 18:55:58.070' union all

select '2014-09-18 18:47:12.960'

select * from #test

----This is instead of the BETWEEN

where

createdon <= '2014-09-26 23:59:59.999'

and createdon >= '2014-08-01 00:00:00'

drop table #test

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 9

Why are you converting it to a string to do the comparison?

And if you absolutely have to convert to to a string you would need to do a yyyymmdd format for it to handle the range properly.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

BWFC (9/26/2014)
create table #Test ( CreatedOn datetime ,ID int identity(1,1) ) insert into #test select '2014-09-26 14:25:51.310' union all select '2014-09-26 12:46:42.647' union all select '2014-09-24 14:49:35.190' union all select '2014-09-24 12:39:21.150' union all select '2014-09-19 15:22:36.233' union all select '2014-09-19 12:48:07.910' union all select '2014-09-18 18:55:58.070' union all select '2014-09-18 18:47:12.960' select * from #test ----This is instead of the BETWEEN where createdon <= '2014-09-26 23:59:59' and createdon >= '2014-08-01 00:00:00'
drop table #test

Where does '2014-09-26 23:59:59.003' fit into this?

where

createdon < '2014-09-27 00:00:00

and createdon >= '2014-08-01 00:00:00'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Neil Burton SSC-Insane Points: 23008 More actions · Answer 11

Good spot Chris. I'd mentally accounted for it but forgot to actually write it in.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 12

>>>>Remove the CONVERT and you'll be doing the date range check you want.

how do you convert 26/09/2014 to Date ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

spectra (9/26/2014)
>>>>Remove the CONVERT and you'll be doing the date range check you want.
how do you convert 26/09/2014 to Date ?

http://www.sqlservercentral.com/Forums/FindPost1620228.aspx

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Neil Burton SSC-Insane Points: 23008 More actions · Answer 14

spectra (9/26/2014)
>>>>Remove the CONVERT and you'll be doing the date range check you want.
how do you convert 26/09/2014 to Date ?

If your dates are stored as

2014-09-26 14:25:51.310

2014-09-26 12:46:42.647

2014-09-24 14:49:35.190

2014-09-24 12:39:21.150

2014-09-19 15:22:36.233

2014-09-19 12:48:07.910

2014-09-18 18:55:58.070

2014-09-18 18:47:12.960

then you can use Cast(CreatedOn as date)

create table #Test

(

CreatedOn datetime

,ID int identity(1,1)

)

insert into #test

select '2014-09-26 23:59:59.003' union all

select '2014-09-26 12:46:42.647' union all

select '2014-09-24 14:49:35.190' union all

select '2014-09-24 12:39:21.150' union all

select '2014-09-19 15:22:36.233' union all

select '2014-09-19 12:48:07.910' union all

select '2014-09-18 18:55:58.070' union all

select '2014-09-18 18:47:12.960'

select * from #test

----This is instead of the BETWEEN

where

cast(createdon as date) <= '2014-09-26'

and cast(createdon as date) >= '2014-08-01'

drop table #test

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537