December 13, 2016 at 5:10 am
I have data "2016-11-10 12:12:42.100" in table column UPDATED_DATE.
my where clause is ...
WHERE CONVERT(varchar(11),UPDATED_DATE,103) BETWEEN '01/12/2016' and '13/12/2016' order by updated_date desc
This where clause is returning data "2016-11-10 12:12:42.100" in resultset.
I was not expecting the data to be returned since its from the November month.
What fix I require in my where cause ?
December 13, 2016 at 5:21 am
What data type is the UPDATED_DATE column?
John
December 13, 2016 at 5:26 am
John Mitchell-245523 (12/13/2016)
What data type is the UPDATED_DATE column?John
datetime
December 13, 2016 at 5:31 am
WHERE UPDATED_DATE BETWEEN '20161201' and '20161213'
Bear in mind that this won't capture anything that happened on 13th, unless it happened at midnight (20161213 00:00:00). If you want to capture that, convert UPDATED_DATE to date.
John
December 13, 2016 at 5:35 am
John Mitchell-245523 (12/13/2016)
WHERE UPDATED_DATE BETWEEN '20161201' and '20161213'Bear in mind that this won't capture anything that happened on 13th, unless it happened at midnight (20161213 00:00:00). If you want to capture that, convert UPDATED_DATE to date.
John
could not get you.
what changes I need to do in my where clause so that data ( example data given) which does not belong to the range does not return.
December 13, 2016 at 5:37 am
John Mitchell-245523 (12/13/2016)
WHERE UPDATED_DATE BETWEEN '20161201' and '20161213'Bear in mind that this won't capture anything that happened on 13th, unless it happened at midnight (20161213 00:00:00). If you want to capture that, convert UPDATED_DATE to date.
John
Another form of John's post, but excluding rows at midnight on the 13th.
WHERE UPDATED_DATE >= '20161201'
AND UPDATED_DATE < '20161213'
The important point here is to not apply a CONVERT function on the column. SQL is good at handling dates if you handle them as dates.
Also, putting a function on a column in a predicate means that SQL Server has to calculate the result of the function for every row...in the table. It's not every row in the results set because the result set hasn't been built yet. This is called a non-SARGable predicate and will lead to horrible performance because the whole column has to be read and then the values calculated. In short, avoid it.
December 13, 2016 at 5:42 am
CREATE TABLE #John (j datetime)
INSERT INTO #John VALUES ('2016-11-10 12:12:42.100')
SELECT j FROM #John
WHERE j BETWEEN '20161201' and '20161213'
[font="Courier New"](0 row(s) affected)[/font]
John
December 13, 2016 at 6:16 am
John Mitchell-245523 (12/13/2016)
CREATE TABLE #John (j datetime)INSERT INTO #John VALUES ('2016-11-10 12:12:42.100')
SELECT j FROM #John
WHERE j BETWEEN '20161201' and '20161213'
[font="Courier New"](0 row(s) affected)[/font]
John
Tested .... but its not working ....I had some data on 7 DEC i.e 2016-12-07 13:41:19.727
This should have returned from your query...but its not returning although its between 20161201 and 20161213
December 13, 2016 at 6:19 am
spectra (12/13/2016)
John Mitchell-245523 (12/13/2016)
CREATE TABLE #John (j datetime)INSERT INTO #John VALUES ('2016-11-10 12:12:42.100')
SELECT j FROM #John
WHERE j BETWEEN '20161201' and '20161213'
[font="Courier New"](0 row(s) affected)[/font]
John
Tested .... but its not working ....I had some data on 7 DEC i.e 2016-12-07 13:41:19.727
This should have returned from your query...but its not returning although its between 20161201 and 20161213
Please post up your query. It's frustrating for people who are trying to help you, having to guess what your query looks like.
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
December 13, 2016 at 6:24 am
<scrapped>
December 13, 2016 at 6:26 am
my mistake.... its working..
December 13, 2016 at 6:29 am
I would like to upgrade this query a bit ...
CREATE TABLE #John (j datetime)
INSERT INTO #John VALUES ('2016-11-10 12:12:42.100')
SELECT j FROM #John
WHERE j BETWEEN '20161201' and '20161213'
If I have data on 13th I want to return that well.....what changes I need to add to this query. I don't want to change datetime to any other datatype e.g date .......Is there any way ?
December 13, 2016 at 6:48 am
spectra (12/13/2016)
I would like to upgrade this query a bit ...CREATE TABLE #John (j datetime)
INSERT INTO #John VALUES ('2016-11-10 12:12:42.100')
SELECT j FROM #John
WHERE j BETWEEN '20161201' and '20161213'
If I have data on 13th I want to return that well.....what changes I need to add to this query. I don't want to change datetime to any other datatype e.g date .......Is there any way ?
Make the upper bound "less than the 14th"
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
December 13, 2016 at 7:11 am
spectra (12/13/2016)
I don't want to change datetime to any other datatype e.g date .......
Why not? You're not changing any data if you cast as date. I understand it's still sargable, as well, although I haven't actually tested. Or just do it as Chris suggested.
SELECT j FROM #John
WHERE CAST(j AS date) BETWEEN '20161201' and '20161213'
John
December 13, 2016 at 7:29 am
The problem is that you are converting the date to DD/MM/YYYY format, and that doesn't work properly with range logic. You need to use YYYY/MM/DD format or don't convert at all.
DECLARE @UPDATED_DATE DATETIME = '2016-11-10 12:12:42.100';
PRINT CONVERT(varchar(11),@UPDATED_DATE,103);
IF CONVERT(varchar(11),@UPDATED_DATE,103) BETWEEN '01/12/2016' and '13/12/2016'
PRINT 'WRONG'
ELSE PRINT 'RIGHT';
10/11/2016
WRONG
CONVERT(varchar(8),@UPDATED_DATE,112) BETWEEN '20161201' and '20161213'
or even better:
@UPDATED_DATE >= '20161201' and @UPDATED_DATE < '20161214'
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply