Comparing Results with Today's Date

Comparing results with today's date?

There is no native Now() function in SQL Server so you should use:

select GETDATE() --2012-05-01 10:14:13.403

you can get day, month and year separately by doing:

select DAY(getdate())  --1
select month(getdate())  --5
select year(getdate()) --2012

if you are on sql server 2008, there is the DATE date time which has only the date part, not the time:

select cast (GETDATE() as DATE) --2012-05-01

how to compare a given date with current date in sql

Firstly, you have specified that column submitDate is of datatype Date while, as per your question its datatype should be Timestamp.When the datatype of submitDate column is Date, there is no reason to even compare the time.

However if you need to still want to compare the submitDate with current timestamp, you can do it this way:
select * from submitDate where date_format(submitDate,'%d/%m/%y %T') <= now();

Edit: The above query is for Mysql

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

SQL Server Current Date compare with specific date

If PostDate is DATE or DATETIME, instead of casting you could use DATEDIFF function to get the days between two dates and do the INT comparison:

WHERE DATEDIFF(DAY, PostDate, GETDATE())>2

If PostDate is varchar, stored in the format shown in the OP:

SET LANGUAGE british

SELECT ....
WHERE DATEDIFF(DAY, CAST(PostDate as datetime), GETDATE())>2

EDIT: Apparently DATEDIFF will work if PostDate is VARCHAR data type as well

DECLARE @PostDate VARCHAR(50)

SET @PostDate='08-01-2017'

SELECT DATEDIFF(DAY, @PostDate, GETDATE()) -- GETDATE() is 08-08-2017

-- Returns 7

Having said this, it is a good practice to keep Dates and Times as proper data types. In your case, you could change the data type to DATE, if possible. Will speed up lookups

EDIT 2: Please note, SQL Server works with ISO 8601 Date Format, which is YYYY-MM-DD, but the dates in OP's example, even though as per OP refer to dates in August 2017, are given incorrectly (referring to Jan and Feb 2017) and are stored as varchar. For correct results, these need to be either converted to DATE/DATETIME data type, or reformatted with the correct ISO format.

EDIT 3: Showing an example of casting OP's date format into proper, ISO format before calling DATEDIFF:

SET LANGUAGE british

DECLARE @PostDate VARCHAR(50)

SET @PostDate='2017-01-08'

SELECT DATEDIFF(DAY, CAST(@PostDate AS DATETIME), GETDATE()) -- GETDATE() is 08-08-2017

-- Returns 7

And the WHERE clause would be as follows:

-- In the begining of the select statement

SET LANGUAGE british

SELECT *
FROM ...
WHERE DATEDIFF(DAY, CAST(PostDate as datetime), GETDATE())>2

How to compare current date with a date range or discrete date values in MySQL?

I would definitely use find_in_set() and substring_index(), if I had such a lousy data structure:

select t.*
from t
where find_in_set(date_format(curdate(), '%Y-%m-%d'), datecol) > 0 or
      (datecol like '%,%' and datecol not like '%,%,%' and
       date_format(curdate(), '%Y-%m-%d') between substring_index(datecol, ',', 1) and substring_index(datecol, ',', -1)
      );

compare string with today's date in JavaScript

    <script type="text/javascript">

var q = new Date();
var m = q.getMonth()+1;
var d = q.getDay();
var y = q.getFullYear();

var date = new Date(y,m,d);

mydate=new Date('2011-04-11');
console.log(date);
console.log(mydate)

if(date>mydate)
{
    alert("greater");
}
else
{
    alert("smaller")
}

</script>

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.

---

Related Topics