How to Select Rows for a Specific Date, Ignoring Time in SQL Server

How to select rows for a specific date, ignoring time in SQL Server

You can remove the time component when comparing:

SELECT * 
FROM sales 
WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, salesDate))) = '11/11/2010'

Another approach is to change the select to cover all the time between the start and end of the date:

SELECT * 
FROM sales 
-- WHERE salesDate BETWEEN '11/11/2010 00:00:00.00' AND '11/11/2010 23:59:59.999'
WHERE salesDate BETWEEN '2020-05-18T00:00:00.00' AND '2020-05-18T23:59:59.999'

How to select date without time in SQL

I guess he wants a string.

select convert(varchar(10), '2011-02-25 21:17:33.933', 120)

120 here tells the convert function that we pass the input date in the following format: yyyy-mm-dd hh:mi:ss.

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)

Get rows having a datetime less than or equal to today ignoring time

Simply, just cast both, your column and todays date to DATE:

SELECT * FROM MyTable
WHERE CAST(dt AS DATE) <= CAST(GETDATE() AS DATE)

how to query where date with time = date without time in ms sql

Try like this

SELECT * FROM  Bookings WHERE Convert(VARCHAR(10),StartTime,101) =  Convert(Varchar(10),'2/15/2014',101)

If you are using SQL SERVER 2012

Try this

 SELECT * FROM  Bookings WHERE FORMAT(StartTime,'M/dd/yyyy') = FORMAT('2/15/2014','M/dd/yyyy')

SQL FORMAT

How to only check the time on datetime fields but ignore the date?

You only need a minor tweak on what you already have.

SELECT  *
FROM    progen.DY
WHERE   TIME(DY_DATE) <> '00:00:00:000'

Use CONVERT to change your DATETIME to a TIME.

SELECT  *
FROM    progen.DY
WHERE   CONVERT(TIME, DY_DATE) <> '00:00:00:000'

Compare dates in T-SQL, ignoring the time part

The most reasonable way to do this is to strip away the time portion of the datetime values and compare the results, and the best way to strip the time portion from a datetime is like this:

cast(current_timestamp as date)    

I used to use and advocate a process that looked like one of the following two lines:

cast(floor(cast(getdate() as float)) as datetime)
dateadd(dd,0, datediff(dd,0, getDate()))

But now that Sql Server has the Date type, which does not hold a time component, there is little reason to use either of those techniques.

One more thing to keep in mind is this will still bog down a query if you need to do it for two datetime values for every row in a where clause or join condition. If possible you want to factor this out somehow so it's pre-computed as much as possible, for example using a view or computed column.

Finally, note the DATEDIFF function compares the number of boundaries crossed. This means the datediff in days between '2009-09-14 11:59:59' and '2009-09-15 00:00:01' is 1, even though only 2 seconds has elapsed, but the DATEDIFF in days between '2009-09-15 00:00:01' and '2009-09-15 11:59:59' is still zero, even though 86,398 seconds elapsed. It doesn't really care at all about the time portion there, only the boundaries. Depending on what your query is trying to do, you might be able to use that to your advantage.

---

Related Topics