October 6, 2017 at 9:24 am
sgmunson - Friday, October 6, 2017 8:31 AMaloshya - 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).
Now i changed it to date
Even, Whats the correct way to search in date datatype , 01/01/2012 or 01-01-2012
October 6, 2017 at 9:27 am
aloshya - Friday, October 6, 2017 9:24 AMEven, Whats the correct way to search in date datatype , 01/01/2012 or 01-01-2012
Neither. The universally recognised format is 20120101.
John
October 6, 2017 at 9:28 am
aloshya - Friday, October 6, 2017 9:24 AMsgmunson - Friday, October 6, 2017 8:31 AMaloshya - 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).
Now i changed it to date
Even, Whats the correct way to search in date datatype , 01/01/2012 or 01-01-2012
Use '20120101' ('YYYYMMDD'). The SQL Server query processor implicitly converts that into a DATE for you.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 6, 2017 at 9:28 am
aloshya - Friday, October 6, 2017 9:24 AMsgmunson - Friday, October 6, 2017 8:31 AMaloshya - 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).
Now i changed it to date
Even, Whats the correct way to search in date datatype , 01/01/2012 or 01-01-2012
Use YYYYMMDD or YYYY-MM-DD. The former is an ISO standard, if that helps.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 6, 2017 at 9:39 am
All advices offered above are valid. However, it is possible to write the query with only one date - the one you want.
Once, I had similar problem. The first approach was exactly what you tried, and exactly the same way - it did not work
SELECT * FROM Audit_Codebooks
WHERE UpdateDate = '2012-09-13'
-- (0 row(s) affected)
The problem was column UpdateDate was defined as DateTime, which stored values with minutes,seconds and parts of a second, like ths '2012-09-13 10:20:58.067'. That is not equal to '2012-09-13', regardless of actual data types. Since you are probably using version of SQL > 2012, you can do what we did - casted UpdateDate to Date.
After we casted UpdateDate to Date, the query returned 800+ rows:
SELECT
*
FROM Audit_Codebooks
WHERE CAST(UpdateDate AS DAte) = '2012-09-13'
;
-- -- (862 row(s) affected)
If your version of T SQL does not suport type Date (date without minute/seconds etc), you can find easily on the web how to use ony date part for given column.
I hope this solved the problem
🙂
October 6, 2017 at 10:02 am
Zidar - Friday, October 6, 2017 9:39 AMAll advices offered above are valid. However, it is possible to write the query with only one date - the one you want.Once, I had similar problem. The first approach was exactly what you tried, and exactly the same way - it did not work
SELECT * FROM Audit_Codebooks
WHERE UpdateDate = '2012-09-13'
-- (0 row(s) affected)
The problem was column UpdateDate was defined as DateTime, which stored values with minutes,seconds and parts of a second, like ths '2012-09-13 10:20:58.067'. That is not equal to '2012-09-13', regardless of actual data types. Since you are probably using version of SQL > 2012, you can do what we did - casted UpdateDate to Date.After we casted UpdateDate to Date, the query returned 800+ rows:
SELECT
*
FROM Audit_Codebooks
WHERE CAST(UpdateDate AS DAte) = '2012-09-13'
;
-- -- (862 row(s) affected)If your version of T SQL does not suport type Date (date without minute/seconds etc), you can find easily on the web how to use ony date part for given column.
I hope this solved the problem
🙂
Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
October 6, 2017 at 10:27 am
John Mitchell-245523 - Friday, October 6, 2017 10:02 AMZidar - Friday, October 6, 2017 9:39 AMAll advices offered above are valid. However, it is possible to write the query with only one date - the one you want.Once, I had similar problem. The first approach was exactly what you tried, and exactly the same way - it did not work
SELECT * FROM Audit_Codebooks
WHERE UpdateDate = '2012-09-13'
-- (0 row(s) affected)
The problem was column UpdateDate was defined as DateTime, which stored values with minutes,seconds and parts of a second, like ths '2012-09-13 10:20:58.067'. That is not equal to '2012-09-13', regardless of actual data types. Since you are probably using version of SQL > 2012, you can do what we did - casted UpdateDate to Date.After we casted UpdateDate to Date, the query returned 800+ rows:
SELECT
*
FROM Audit_Codebooks
WHERE CAST(UpdateDate AS DAte) = '2012-09-13'
;
-- -- (862 row(s) affected)If your version of T SQL does not suport type Date (date without minute/seconds etc), you can find easily on the web how to use ony date part for given column.
I hope this solved the problem
🙂
Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
And there are always exceptions. If the column is defined as a datetime data type, that version is still SARGable.
October 6, 2017 at 5:42 pm
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,
There's gotta be a 'bot running loose somewhere around here. Why anyone would give a LIKE to using VARCHAR as a date column, even one in yyyymmdd format, is totally beyond me.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2017 at 7:55 am
John Mitchell-245523 - Friday, October 6, 2017 10:02 AMZidar - Friday, October 6, 2017 9:39 AMAll advices offered above are valid. However, it is possible to write the query with only one date - the one you want.Once, I had similar problem. The first approach was exactly what you tried, and exactly the same way - it did not work
SELECT * FROM Audit_Codebooks
WHERE UpdateDate = '2012-09-13'
-- (0 row(s) affected)
The problem was column UpdateDate was defined as DateTime, which stored values with minutes,seconds and parts of a second, like ths '2012-09-13 10:20:58.067'. That is not equal to '2012-09-13', regardless of actual data types. Since you are probably using version of SQL > 2012, you can do what we did - casted UpdateDate to Date.After we casted UpdateDate to Date, the query returned 800+ rows:
SELECT
*
FROM Audit_Codebooks
WHERE CAST(UpdateDate AS DAte) = '2012-09-13'
;
-- -- (862 row(s) affected)If your version of T SQL does not suport type Date (date without minute/seconds etc), you can find easily on the web how to use ony date part for given column.
I hope this solved the problem
🙂
Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
Zidar - Friday, October 6, 2017 9:39 AMAll advices offered above are valid. However, it is possible to write the query with only one date - the one you want.
After we casted UpdateDate to Date, the query returned 800+ rows:SELECT
*
FROM Audit_Codebooks
WHERE CAST(UpdateDate AS DAte) = '2012-09-13'
;
-- -- (862 row(s) affected)🙂
Don't do it that way even if it does "work". It makes a "non-SARGable" predicate where the query is forced to, at best, do an index scan rather than a seek. The reason why the pro's on this thread are using the 2 date version is because it's actually the proper way to do it to allow the code to do an index seek.,
I agree that, as a human, you won't see much difference in performance on 800 rows but the method you used is a really bad habit to get into because it will make a serious difference in high hit queries and large batch processing..
{EDIT} My apologies. I had forgotten that you CAN do this with DATE and still have the column be SARGable in 2016.
As a bit of a sidebar, I'll probably never get used to that exception and will likely continue to use the 2 part date check.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2017 at 9:36 am
John Mitchell-245523 - Friday, October 6, 2017 10:02 AMZidar - Friday, October 6, 2017 9:39 AMAll advices offered above are valid. However, it is possible to write the query with only one date - the one you want.Once, I had similar problem. The first approach was exactly what you tried, and exactly the same way - it did not work
SELECT * FROM Audit_Codebooks
WHERE UpdateDate = '2012-09-13'
-- (0 row(s) affected)
The problem was column UpdateDate was defined as DateTime, which stored values with minutes,seconds and parts of a second, like ths '2012-09-13 10:20:58.067'. That is not equal to '2012-09-13', regardless of actual data types. Since you are probably using version of SQL > 2012, you can do what we did - casted UpdateDate to Date.After we casted UpdateDate to Date, the query returned 800+ rows:
SELECT
*
FROM Audit_Codebooks
WHERE CAST(UpdateDate AS DAte) = '2012-09-13'
;
-- -- (862 row(s) affected)If your version of T SQL does not suport type Date (date without minute/seconds etc), you can find easily on the web how to use ony date part for given column.
I hope this solved the problem
🙂
Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
October 7, 2017 at 9:45 am
sgmunson - Friday, October 6, 2017 9:28 AMaloshya - Friday, October 6, 2017 9:24 AMsgmunson - Friday, October 6, 2017 8:31 AMaloshya - 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).
Now i changed it to date
Even, Whats the correct way to search in date datatype , 01/01/2012 or 01-01-2012Use YYYYMMDD or YYYY-MM-DD. The former is an ISO standard, if that helps.
is it applicable everywhere for date related fucntion like datepart,datediff etc
select * ,DATEDIFF (day,P.LASTCHANGED,'20170221') as YY
from [SRV-RVS].dbo.EMP as p
October 7, 2017 at 9:47 am
aloshya - Saturday, October 7, 2017 9:36 AMJohn Mitchell-245523 - Friday, October 6, 2017 10:02 AMBe careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used – leading to an expensive scan rather than (potentially) a seek.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 7, 2017 at 10:19 am
Phil Parkin - Saturday, October 7, 2017 9:47 AMaloshya - Saturday, October 7, 2017 9:36 AMJohn Mitchell-245523 - Friday, October 6, 2017 10:02 AMBe careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used – leading to an expensive scan rather than (potentially) a seek.
Now the real dilemma is how to write a better query. i always focus on returning the result but was unsure of other areas like query performance.
October 7, 2017 at 3:25 pm
Phil Parkin - Saturday, October 7, 2017 9:47 AMaloshya - Saturday, October 7, 2017 9:36 AMJohn Mitchell-245523 - Friday, October 6, 2017 10:02 AMBe careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used – leading to an expensive scan rather than (potentially) a seek.
Except - as Lynn pointed out - CASTing DATETIME column to DATE. It's SARGable.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 7, 2017 at 9:10 pm
ChrisM@home - Saturday, October 7, 2017 3:25 PMPhil Parkin - Saturday, October 7, 2017 9:47 AMaloshya - Saturday, October 7, 2017 9:36 AMJohn Mitchell-245523 - Friday, October 6, 2017 10:02 AMBe careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used – leading to an expensive scan rather than (potentially) a seek.Except - as Lynn pointed out - CASTing DATETIME column to DATE. It's SARGable.
Jeez... after more than 20 years of doing it the other way, I'm probably never going to get used to that exception. I went back and corrected my post above.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply