October 6, 2017 at 7:34 am
I Have issues in selecting records when using date in my where clause1 ) I am trying to fetch records for a particular date
SELECT [ID]
,[LASTNAME]
,[FIRSTNAME]
,[MIDNAME]
,[REGNO]
,[LASTCHANGED]
FROM [SRV-RVS].[dbo].[EMP]
where LASTCHANGED = '2015/09/17'
The above query returns no result
2 ) but when i use the below where clause
where LASTCHANGEDLASTCHANGED BETWEEN '2015/09/17' AND '2015/09/18'
It returned 81 records for 2015/09/17
How to retrieve records for the particular date using SQL query
October 6, 2017 at 7:38 am
Your 1st query uses 2015/09/30 in the where clause and your 2nd has no mention of 2015/09/30 so 81 records for 2015/09/17 is probably correct.
Thanks
October 6, 2017 at 7:41 am
NorthernSoul - Friday, October 6, 2017 7:38 AMYour 1st query uses 2015/09/30 in the where clause and your 2nd has no mention of 2015/09/30 so 81 records for 2015/09/17 is probably correct.Thanks
Updated the question.
The first query was for 2015/09/17
October 6, 2017 at 7:41 am
aloshya - Friday, October 6, 2017 7:33 AMHow to retrieve records for the particular date using SQL query
Roughly how you have done in your second WHERE clause, which will return all rows from 2015/09/17 00:00:00 to 2015/09/18 00:00:00 inclusive. If you want to exclude 2015/09/18 00:00:00, do it like this:
WHERE LASTCHANGED >= '2015/09/17 00:00:00'
AND LASTCHANGED < '2015/09/18 00:00:00'
John
October 6, 2017 at 7:41 am
aloshya - Friday, October 6, 2017 7:33 AMI Have issues in selecting records when using date in my where clause1 ) I am trying to fetch records for a particular date
SELECT [ID]
,[LASTNAME]
,[FIRSTNAME]
,[MIDNAME]
,[REGNO]
,[LASTCHANGED]
FROM [SRV-RVS].[dbo].[EMP]
where LASTCHANGED = '2015/09/30'The above query returns no result
2 ) but when i use the below where clause
where LASTCHANGEDLASTCHANGED BETWEEN '2015/09/17' AND '2015/09/18'It returned 81 records for 2015/09/17
How to retrieve records for the particular date using SQL query
Use
WHERE LASTCHANGED >= '20150917' AND LASTCHANGED <'20150918'
Don't use BETWEEN here, because it includes both endpoints.
Note also the use of ISO date format for literal dates.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 6, 2017 at 7:53 am
John Mitchell-245523 - Friday, October 6, 2017 7:41 AMaloshya - Friday, October 6, 2017 7:33 AMHow to retrieve records for the particular date using SQL queryRoughly how you have done in your second WHERE clause, which will return all rows from 2015/09/17 00:00:00 to 2015/09/18 00:00:00 inclusive. If you want to exclude 2015/09/18 00:00:00, do it like this:
WHERE LASTCHANGED >= '2015/09/17 00:00:00'
AND LASTCHANGED < '2015/09/18 00:00:00'John
I find it hard to use time in search field , is there any alternative and below @phil has shown one way to do it.
October 6, 2017 at 7:55 am
Phil Parkin - Friday, October 6, 2017 7:41 AMaloshya - Friday, October 6, 2017 7:33 AMI Have issues in selecting records when using date in my where clause1 ) I am trying to fetch records for a particular date
SELECT [ID]
,[LASTNAME]
,[FIRSTNAME]
,[MIDNAME]
,[REGNO]
,[LASTCHANGED]
FROM [SRV-RVS].[dbo].[EMP]
where LASTCHANGED = '2015/09/30'The above query returns no result
2 ) but when i use the below where clause
where LASTCHANGEDLASTCHANGED BETWEEN '2015/09/17' AND '2015/09/18'It returned 81 records for 2015/09/17
How to retrieve records for the particular date using SQL query
Use
WHERE LASTCHANGED >= '20150917' AND LASTCHANGED <'20150918'
Don't use BETWEEN here, because it includes both endpoints.
Note also the use of ISO date format for literal dates.
It returns the result for that particular date,
even why we want to pass two dates for selecting records for one particular date.
October 6, 2017 at 7:59 am
aloshya - Friday, October 6, 2017 7:55 AMIt returns the result for that particular date,
even why we want to pass two dates for selecting records for one particular date.
One particular date is actually one particular point in time. That's why you need two of them: a beginning point and an end point. If you want your dates to be discrete instead of continuous, change the data type of the column from datetime to date.
John
October 6, 2017 at 8:03 am
John Mitchell-245523 - Friday, October 6, 2017 7:59 AMaloshya - Friday, October 6, 2017 7:55 AMIt returns the result for that particular date,
even why we want to pass two dates for selecting records for one particular date.One particular date is actually one particular point in time. That's why you need two of them: a beginning point and an end point. If you want your dates to be discrete instead of continuous, change the data type of the column from datetime to date.
John
My date column is varchar,
October 6, 2017 at 8:08 am
aloshya - Friday, October 6, 2017 8:03 AMJohn Mitchell-245523 - Friday, October 6, 2017 7:59 AMaloshya - Friday, October 6, 2017 7:55 AMIt returns the result for that particular date,
even why we want to pass two dates for selecting records for one particular date.One particular date is actually one particular point in time. That's why you need two of them: a beginning point and an end point. If you want your dates to be discrete instead of continuous, change the data type of the column from datetime to date.
John
My date column is varchar,
You shouldn't be using varchar to store a date. Are you able to change this and use one of the date data types?
Thanks
October 6, 2017 at 8:10 am
aloshya - Friday, October 6, 2017 8:03 AMMy date column is varchar,
Same difference. It'll be implicitly converted to datetime. This is an example of why tables should be designed with the correct data types.
John
October 6, 2017 at 8:20 am
John Mitchell-245523 - Friday, October 6, 2017 8:10 AMaloshya - Friday, October 6, 2017 8:03 AMMy date column is varchar,Same difference. It'll be implicitly converted to datetime. This is an example of why tables should be designed with the correct data types.
John
I can change that and usually, I am comfortable with datatypes except for date . : 🙂
October 6, 2017 at 8:24 am
aloshya - Friday, October 6, 2017 8:03 AMJohn Mitchell-245523 - Friday, October 6, 2017 7:59 AMaloshya - Friday, October 6, 2017 7:55 AMIt returns the result for that particular date,
even why we want to pass two dates for selecting records for one particular date.One particular date is actually one particular point in time. That's why you need two of them: a beginning point and an end point. If you want your dates to be discrete instead of continuous, change the data type of the column from datetime to date.
John
My date column is varchar,
Understand that with varchar data having datetime values, you have no chance of getting it right without converting it to at least a date value, and that will definitely make the query much slower than it would otherwise be, unless your data quantity is trivial. Because of the potential for an invalid datetime value that can occur in a varchar column, continuing to use that data type is probably negligent, at the least. As in terrible, horrible, no good, very bad idea. If you can have that data type changed, you should, even if it means a lot of other work needs to happen. It's a gaping hole that will eventually bite you in the butt far worse than this particular issue will at this point in time. Further down the road, performance could become a rather serious issue as more and more data accumulates.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 6, 2017 at 8:26 am
aloshya - Friday, October 6, 2017 8:20 AMI can change that and usually, I am comfortable with datatypes except for date . : 🙂
Please go ahead and change it. Future developers will thank you!
Post any questions you may have around handling dates here and we will attempt to ease your discomfort 🙂
If you have no need of a time component, use the DATE datatype, as John has already suggested. If you do that, your query to return rows for specific day becomes simpler:
WHERE DATE = 'YYYYMMDD'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 6, 2017 at 8:32 am
aloshya - Friday, October 6, 2017 8:20 AMJohn Mitchell-245523 - Friday, October 6, 2017 8:10 AMaloshya - Friday, October 6, 2017 8:03 AMMy date column is varchar,Same difference. It'll be implicitly converted to datetime. This is an example of why tables should be designed with the correct data types.
John
I can change that and usually, I am comfortable with datatypes except for date . : 🙂
I'd suggest that your data is likely to contain time values in addition to the date. You'll have to take a good close look at your data, and perhaps do the following query to see if any of your data has a problem:SELECT *
FROM yourtable
WHERE TRYCONVERT(datetime, yourcolumnname) IS NULL;
Be sure to use your table name and your column name in place of what I coded. If that query returns 0 rows, then you can probably safely change the data type in place, but for safety, probably a better idea to add a new datetime column, and then populate it using a CONVERT(datetime, existingcolumnname).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply