CODEWITHSUNDEEP
sql-serverdate
Uttam Nandi
Uttam Nandi

Reputation: 127

Comparing dates with current date in Sql server

I have a table which has list of some events with dates. I am trying to write a stored procedure that will return only the upcoming events.

I have written the following query in the stored procedure:

SELECT     *
FROM       Events
WHERE      tDate >= (select CAST(GETDATE() as DATE))

But this is not returning correct result. This is also showing results that have dates less than current date. How to write a query that will return all the events that have date equal or greater than today's date.

Edit: Dates that have been entered on the table have the format yyyy/dd/mm and getdate() returns date in the format yyyy/mm/dd. I think this is causing the problem. Dates that have been entered into the table has been taken using jquery date picker. Any solution to this problem?

Upvotes: 1

Views: 11415

Answers (2)

ughai
ughai

Reputation: 9880

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

Upvotes: 1

Tom V
Tom V

Reputation: 1496

Not sure why you have an additional select

SELECT     *
FROM       Events
WHERE      tDate >= CAST(GETDATE() as DATE)

Upvotes: 2

Related Questions