CODEWITHSUNDEEP
sqlsql-serverdate
Sadat Mainuddin
Sadat Mainuddin

Reputation: 309

Need to identify number that is higher than a given date

I have a date column in a table. The date column is in varchar. I want to identify a particular date range from that date column. My query is like this:

SELECT * 
FROM [003 AccptReg].[dbo].[SysData1]
WHERE [RegDate_Sys] > '18 jul 2013'

But the result is not giving accurate result, i.e. it gives dates which are prior of 18 jul 2013.

Is there any thing wrong I am doing?

Upvotes: 0

Views: 64

Answers (3)

Guffa
Guffa

Reputation: 700422

The problem is that you have the date as a varchar, and doesn't convert it to a date when you are doing the comparison. The database doesn't know that you see the data as dates, and will simply compare them as strings, so for example '2 jan 1736' will be larger than '18 jul 2013' because 2 comes after 1.

The best would be if you could store the data as datetime values (or date), then you don't need to do the conversion when you compare the values, which would give better performance.

If that's not possible, do the conversion in the query:

select * from [003 AccptReg].[dbo].[SysData1]
where convert(datetime, [RegDate_Sys], 106) > '18 jul 2013'

Depending on the settings on the server, you might also need to convert '18 jul 2013' in the same way for the database to understand it correctly as a date.

Upvotes: 1

EricZ
EricZ

Reputation: 6205

For date column, you should compare as DATE

select * from [003 AccptReg].[dbo].[SysData1]
where CAST([RegDate_Sys] AS DATE) > CAST('18 jul 2013' AS DATE)

Upvotes: 2

Sonam
Sonam

Reputation: 3466

Convert the date to datetime format and then compare:

 select * from [003 AccptReg].[dbo].[SysData1]
 where convert(datetime,[RegDate_Sys]) >convert(datetime,'18 jul 2013')

Upvotes: 0

Related Questions