Compare DATETIME and DATE ignoring time portion
Use the CAST
to the new DATE
data type in SQL Server 2008 to compare just the date portion:
IF CAST(DateField1 AS DATE) = CAST(DateField2 AS DATE)
How to compare only date part when delivery date is today
You can try a query like below
select * from (tablename)
where CAST(delivery_date as date) = CAST(getdate() as date)
Also if all delivery dates have time part like 00:00:00.000
for sure then
select * from (tablename)
where delivery_date = CAST(getdate() as date)
would work as good.
Best way to compare dates without time in SQL Server
Don't use convert - that involves strings for no reason. A trick is that a datetime is actually a numeric, and the days is the integer part (time is the decimal fraction); hence the day is the FLOOR of the value: this is then just math, not strings - much faster
declare @when datetime = GETUTCDATE()
select @when -- date + time
declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime)
select @day -- date only
In your case, no need to convert back to datetime; and using a range allows the most efficent comparisons (especially if indexed):
declare @when datetime = 'Feb 15 2012 7:00:00:000PM'
declare @min datetime = FLOOR(CAST(@when as float))
declare @max datetime = DATEADD(day, 1, @min)
select * from sampleTable where DateCreated >= @min and DateCreated < @max
How to return only the Date from a SQL Server DateTime datatype
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
for example
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
gives me
2008-09-22 00:00:00.000
Pros:
- No varchar<->datetime conversions required
- No need to think about locale
Comparing a Date column with a datetime value
This will not yield the same result.
Let's say your DateTo
, which is a DATETIME
value, has a time component:
'2015-09-21 01:00:00'
Your @wkenddate
is '2015-09-21'
. The WHERE DateTo BETWEEN @wkstdate AND @wkenddate
will not retrieve the above row since '2015-09-21 01:00:00'
> @wkenddate
.
For more example:
CREATE TABLE tbl(DateTo DATETIME)
INSERT INTO tbl
SELECT CAST('2015-09-21 00:00:00.000' AS DATETIME) UNION ALL
SELECT CAST('2015-09-21 16:10:49.047' AS DATETIME) UNION ALL
SELECT CAST('2015-09-22 16:10:49.047' AS DATETIME) UNION ALL
SELECT CAST('2015-09-20 16:10:49.047' AS DATETIME)
DECLARE @wkstdate DATE = '20150921',
@wkenddate DATE = '20150921'
SELECT *
FROM tbl
WHERE DateTo BETWEEN @wkstdate AND @wkenddate
SELECT * FROM tbl
WHERE (CONVERT(DATE,DateTo) BETWEEN @wkstdate AND @wkenddate)
DROP TABLE tbl
Now, using function in WHERE
clause does make your query un-SARGable but there are exceptions. One of them is CAST
ing to DATE
.
Another alternative if you do not want to CAST
to DATE
is to not use the BETWEEN
operator. Instead use >= and <
:
WHERE
DateTo >= @wkstdate
AND DateTo < DATEADD(DAY, 1, @wkenddate)
Compare only DATE from 2 datetime columns - sql
Another way:
Select * from Admissions
Where DATEDIFF(dd,AdmitDateTime,DepartDateTime)=0
SQL Server date comparisons based on month and year only
To handle inequalities, such as between, I like to convert date/times to a YYYYMM representation, either as a string or an integer. For this example:
DECLARE @date1 DATETIME = CAST('6/14/2014' AS DATETIME),
@date2 DATETIME = CAST('6/15/2014' AS DATETIME),
@date3 DATETIME = CAST('7/1/2014' AS DATETIME);
SELECT * FROM tableName WHERE @date2 BETWEEN @date1 AND @date3;
I would write the query as:
SELECT *
FROM tableName
WHERE year(@date2) * 100 + month(@date2) BETWEEN year(@date1) * 100 + month(@date1) AND
year(@date3) * 100 + month(@date1);
Compare Date saved as varchar with DateTime
I solved the problem using
DATEADD(SECOND, CONVERT(INT, Left(SUBSTRING(JSON_VALUE(jsonStr, '$.EndDate'), 7, 13), 10)), '19700101'
Related Topics
How to Make SQL Query Result Show With 2 Decimals
How to Compare Timestamp Dates With Date-Only Parameter in MySQL
Sql Server How to Return Null Instead of 0 If a Grouped Value Doesn't Have Rows in Source
Retrieve Varbinary Value as Base64 in Mssql
Calling an API from SQL Server Stored Procedure
Compare 2 Column Values in Same Table
T-Sql Challenges from Hackerrank
Find Out Where MySQL Is Installed on MAC Os X
Convert Julian Date to Yyyy-Mm-Dd
How to See Query History in SQL Server Management Studio
How to Select All the Columns of a Table Except One Column
How to Replace Empty String With Value That Is Not Empty for the Same Policynumber
Sql Server-How to Replace a Date Column With a Current Date When the Column Has a Null Value
How to Search Json Array in MySQL
Simple Check for Select Query Empty Result
Repeat Each Value in SQL Table N Times With 1:N in Different Column