SQL database date column is comparing with current date

Question

My problem is that I want to select records from data base if the date field is greater than current date

I use the following code

SELECT 
    Id, eve_name, eve_date, eve_place,eve_desc 
FROM 
    EventDetails 
WHERE 
    CONVERT(varchar(10), eve_date, 101) >= CONVERT(varchar(10), " + DateTime.Now.ToShortDateString() + ",101)"

but it is not working

I take date column in database as DateTime.. and save the date time using jquery datetime picker

Which database **system** are you using (and what version) - *SQL* is just the structured query language - used by many database systems as their query language.... — marc_s, Jun 06 '13 at 06:35
Do you need the current date where the C# code is running, or would the current date on the database server be acceptable? (Or does it not make a difference?) — Damien_The_Unbeliever, Jun 06 '13 at 06:37

score 1 · Answer 1 · answered Jun 06 '13 at 06:38

1

Assuming the server date is acceptable, and that this is SQL Server (it looks like it since you're using CONVERT), you can just do this in SQL:

SELECT 
    Id, eve_name, eve_date, eve_place,eve_desc 
FROM 
    EventDetails 
WHERE 
    eve_date >= DATEADD(day,DATEDIFF(day,0,CURRENT_TIMESTAMP),0)

And this can also use an index on eve_date, if one is available - because I'm no longer applying any functions to it. I'm using DATEADD/DATEDIFF to remove the time component from the current datetime (obtained via CURRENT_TIMESTAMP).

answered Jun 06 '13 at 06:38

Damien_The_Unbeliever

234,701
27
340
448

@Pranav - because it contains a time component, and I assumed that the OP was doing the `CONVERT`s to try to eliminate the time component. – Damien_The_Unbeliever Jun 06 '13 at 06:50
_My problem is that I want to select records from data base if the date field is greater than current date_ , Hence OP wants only those records which has greater date than current date – Pranav Jun 06 '13 at 06:53
@Pranav - Is "current date" 06/06/2013 or "06:54 UTC on 06/06/2013" - my interpretation is the former, yours apparently is the latter. – Damien_The_Unbeliever Jun 06 '13 at 06:54
(SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) – Vinay Jun 06 '13 at 06:59
2

@Vinay - I'm not sure what you're trying to indicate with your comment. – Damien_The_Unbeliever Jun 06 '13 at 07:01
@Damien It would just return as 2013-06-06 00:00:00.000 as time field would be all zeros. – Vinay Jun 06 '13 at 07:06
@Vinay - and is almost identical to `DATEADD(day,DATEDIFF(day,0,CURRENT_TIMESTAMP),0)` which I already have in my answer. I avoid the abbreviations for the time components since I personally find them annoying (and I try to write for readability rather than terseness) and I tend to use `CURRENT_TIMESTAMP` rather then `GetDate()` (despite them being synonymous) since `CURRENT_TIMESTAMP` is portable (even in situations, as here, where the rest of the query may not be portable) – Damien_The_Unbeliever Jun 06 '13 at 07:09
@Pranav The OP writes he wants dates greater than, but his own try-out query uses the operator `>=` – TT. Jun 06 '13 at 07:33

TT. · Answer 2 · 2013-06-06T08:07:53.443

If you are using SQL Server, you can use the GETDATE() function. This returns the current date/time. If your date-time fields only contain a DATE part, you will need to strip the time part of the result of GETDATE().

SELECT
  *
FROM
  eventdetails
WHERE
  eve_date>=CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME);

You will see that the time part is stripped of the result of GETDATE() by casting it to float, flooring it and casting it back to DATETIME type. There are other ways of doing this, cf Damien's solution. Since DATETIME under the hood is stored as a FLOAT, only one trivial function is called (FLOOR), and performs better than the DATEADD/DATEDIFF trick.

If the eve_date can also contain a TIME part, and you want to compare only the DATE part, you will also have to CAST/CONVERT the eve_date to make the comparison:

SELECT
  *
FROM
  eventdetails
WHERE
  CAST(FLOOR(CAST(eve_date AS FLOAT)) AS DATETIME)>=CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME);

Now this is all written in the perspective of SQL Server 2005 and below, where a DATE type does not exist. For SQL Server 2008 and above, casting to the DATE type will strip the TIME part away as well:

SELECT
  *
FROM
  eventdetails
WHERE
  CAST(eve_date AS DATE)>=CAST(GETDATE() AS DATE);

Note that the GETDATE function is the equivalent of CURRENT_TIMESTAMP function as defined by ANSI SQL standard.

Pranav · Accepted Answer · 2013-06-06T06:54:48.337

0

Try this,

it is simple and fulfills your requirement

:-
Assuming you are using SQL SERVER

   SELECT 
        Id, eve_name, eve_date, eve_place,eve_desc 
    FROM 
        EventDetails 
    WHERE 
         eve_date >= getdate()

Since in Database eve_date column type is Datetime ,so there should not be any problem.

edited Jun 06 '13 at 06:54

answered Jun 06 '13 at 06:37

Pranav

8,563
4
26
42

Dangerous if date only contains a DATE part. GETDATE() returns a time part! – TT. Jun 06 '13 at 07:26
How this will be Dangerous if the column type is datetime in DB as OP has already mentioned in the question.. – Pranav Jun 06 '13 at 07:28
1

Because the expression `'2012-06-06'>='2012-06-06 09:30:16.003'` evaluates as false. Ok, dangerous is not the proper term. The way it is written is incorrect. – TT. Jun 06 '13 at 07:31
The point is column eve_date is datetime in DB, so it will never happen the case as you describe above (2012-06-06) – Pranav Jun 06 '13 at 07:33
The expression `'2012-06-06 00:00:00.000'='2012-06-06'` evaluates as true. It was just an illustration of course. SQL Server converts type DATE or a string that has a DATE in it, to DATETIME if required. – TT. Jun 06 '13 at 07:35
@Pranav Since the OP uses CONVERT with style 101 (mm/dd/yyyy), he/she is trying to compare dates even though the data type is DATETIME. Your query compares date+time rather than just the date, and therefore this is probably not what the OP wanted. – TT. Jun 06 '13 at 10:22
Then extract the date part from the datetime before comparing. See [this thread](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) for how. – bart Jun 06 '13 at 12:03

Freelancer · Answer 4 · 2013-06-06T06:56:20.043

Change datatype inside CONVERT to datetime from varchar

Try This SQL QUERY:

SELECT 
    Id, eve_name, eve_date, eve_place,eve_desc 
FROM 
    EventDetails 
WHERE 
   eve_date>= CONVERT(datetime, '@dtp',101)

C# code avaoiding SQL Injection:

try
{
  cmd=new SqlCommand(SELECT Id, eve_name, eve_date, eve_place,eve_desc FROM         EventDetails WHERE eve_date >= CONVERT(datetime, '@dtp',101),con);
  da=new SqlDataAdapter(cmd);
  cmd.Parameters.AddWithValue("@dtp", DateTime.Now.ToShortDateString());
  ds=new DataSet();
  da.fill(ds);

  //Further Processing

}
catch(Exception ex)
{
   MessageBox.Show(ex.Message);
}

This will work.

why unnecessary conversion to datetime when eve_date is itself datetime type in DB..? — Pranav, Jun 06 '13 at 06:46

SQL database date column is comparing with current date

4 Answers4