How to Compare Dates in SQL Server

Query comparing dates in SQL

Instead of '2013-04-12' whose meaning depends on the local culture, use '20130412' which is recognized as the culture invariant format.

If you want to compare with December 4^th, you should write '20131204'. If you want to compare with April 12^th, you should write '20130412'.

The article Write International Transact-SQL Statements from SQL Server's documentation explains how to write statements that are culture invariant:

Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. For example, yyyymmdd as 19980924.

EDIT

Since you are using ADO, the best option is to parameterize the query and pass the date value as a date parameter. This way you avoid the format issue entirely and gain the performance benefits of parameterized queries as well.

UPDATE

To use the the the ISO 8601 format in a literal, all elements must be specified. To quote from the ISO 8601 section of datetime's documentation

To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format.

... the fraction of second component is optional. The time component is specified in the 24-hour format.

how to compare two dates in same column in SQL

Try this with LAG function:

select LAG(BuyDate,1) OVER (PARTITION BY ItemName ORDER BY BuyDate asc) previous_date 
     , ItemName
     , BuyDate
     , LAG(BuyDate,1) OVER (PARTITION BY ItemName  ORDER BY BuyDate desc) next_date
from FoodSara_tbl

See the final result: sqlfiddle

OR

Use LAG and LEAD function:

select LAG(BuyDate,1) OVER (PARTITION BY ItemName order by BuyDate) previous_date 
     , ItemName
     , BuyDate
     , LEAD(BuyDate,1) OVER (PARTITION BY ItemName order by BuyDate) next_date
from FoodSara_tbl

See the final result; sqlfiddle

Comparing dates with current date in Sql server

your DATE data is incorrectly stored within Sql Server. When your application passes the string '2015-09-04' and you save that your date column, it is saved as 4th Sept 2015 and not 9th April 2015. Hence your query returns such rows as they are greater than GETDATE().

Example

DECLARE @D VARCHAR(10) = '2015-09-04'
SELECT CONVERT(VARCHAR(20),CONVERT(DATE,@D),109)

you need to fix your data and then use a CONVERT with style when saving dates in your table from application, using something like this. CONVERT(DATE, '20150409',112)

DECLARE @D VARCHAR(10) = '20150409'
SELECT CONVERT(VARCHAR(20),CONVERT(DATE,@D,112),109)

Refer these threads for more info:

Impossible to store certain datetime formats in SQL Server

Cast and Convert

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 in multiple rows

RexTester DEMO (using express 2014) hopefully works in 2012..

Lead(), CTE, and DateDiff are supported, so I can't think why it wouldn't...

WITH CTE AS (
  SELECT ID
       , textField
       , DateField
       , case when datediff(dd,datefield, lead(datefield) over (partition by ID order by DateField ASC)) > 90 
                OR lead(datefield) over (partition by ID order by DateField ASC) is null then 1 
         else 0 end bInclude
  FROM  Dummy_Data)

SELECT ID, textFIeld, DateField, binclude
FROM CTE
WHERE bInclude = 1;

we use LEAD() to look ahead at the next datefield for an ID. If null or if > 90 days we mark that record with a 1; otherwise it's a 0 then we only include the 1's.

Giving us:

+----+----+-------------+---------------------+----------+
|    | ID |  textFIeld  |      DateField      | binclude |
+----+----+-------------+---------------------+----------+
|  1 |  1 | Random Text | 01.05.2018 00:00:00 |        1 |
|  2 |  2 | Random Text | 14.01.2017 00:00:00 |        1 |
|  3 |  2 | Random Text | 01.05.2017 00:00:00 |        1 |
|  4 |  2 | Random Text | 01.02.2018 00:00:00 |        1 |
|  5 |  3 | Random Text | 04.01.2018 00:00:00 |        1 |
+----+----+-------------+---------------------+----------+

C# SQL Server compare date with string

First, you should not store dates as strings.

As Panagiotis Kanavos wrote in his comment - this is a serious bug. You can't sort by such a column, you can't search for date ranges, and most important - you can't control if someone enters an invalid value - nothing is stopping someone from entering "Alfredo" to that column.

For more information, read Aaron Bertrand's Bad habits to kick : choosing the wrong data type.

Second, you should not pass dates from .Net to Sql server as strings. you should pass instances of DateTime as parameters.
The .Net DateTime maps directly to SQL Server's Date.

If you can't change the data type of the column, you can at least convert it to date using the proper convert style (103 in your case).

Here is a better way to do it:

var sql = "select * from TB_RICHIESTE where CONVERT(DATE, [Date], 103) <= @Date";

Then you add the @Date parameter to the SqlCommand:

com.Parameters.Add("@Date", SqlDbType.Date).Value = date.Date;

---

Related Topics