How are you supposed to construct an SQL search term for a column of type "DateTime" using a value containing only a date?
My question is:
I have a type column DateTime
in a SQL table and it contains values as
2013-02-01 10:00:00.000
2013-01-27 16:00:00.000
2013-02-01 14:00:00.000
How do I get the results with a query if I only mention the date in Where
, like:
SELECT *
FROM tablename
WHERE columnName = '2013-02-01'
After running this query, I want to get this result
2013-02-01 10:00:00.000
2013-02-01 14:00:00.000
Please help me with this.
source to share
Since your data is stored over time, you Cast
only need to use those dates.
Try the following:
SELECT *
FROM tablename
WHERE CAST(columnName as DATE) = '2013-02-01'
Depending on your version of SQL Server (if it doesn't support the DATE datatype), you can use this:
SELECT *
FROM tablename
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, columnName)) = DATEADD(dd, 0, DATEDIFF(dd, 0, '2013-02-01'))
EDIT. As others have rightly pointed out, you lose any performance gained by indexing the tables in that column in the example above. This should be what you are looking for, given one input parameter (2013-01-01 in this case):
SELECT *
FROM tablename
WHERE columnName >= CONVERT(datetime,'2013-01-01')
AND columnName < DATEADD(dd, 1, CONVERT(datetime,'2013-01-01'))
Good luck.
source to share
Applying functions to a column will make your query non-sargable and mess up your Cardinality estimate .
Use spacing instead.
select *
from tablename
where columnName >= '20130201' and
columnName < '20130202'
Note: Dateformat is YYYY-MM-DD
not always interpreted the way you think it is in SQL Server .
To avoid ambiguity in interpreting dates, use the generic date format "YYYYMMDD".
set language German
go
select convert(datetime, '1989-06-12'),
convert(datetime, '19890612'),
convert(datetime2, '1989-06-12'),
convert(datetime2, '19890612'),
convert(date, '1989-06-12'),
convert(date, '19890612')
source to share