CODEWITHSUNDEEP
sqlsql-server
user2515629
user2515629

Reputation: 29

Compare varchar text with date format in sql

I created a small table with sample dates.

create table tblSample(checkDate varchar(20));

insert into tblSample values('10-Mar-19');
insert into tblSample values('11-Mar-19');
insert into tblSample values('12-Mar-19');
insert into tblSample values('13-Mar-19');
insert into tblSample values('14-Mar-19');

select * from tblSample;

select * from tblSample where checkDate=convert(varchar(10),getdate(),101);

In the above date was stored as a text.

Now I would like to compare with date field. (compare with current date)

Upvotes: 0

Views: 179

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Here is a really simple solution:

alter table tblSample alter column checkDate date;

That is, don't store dates as strings in your data. Fix the data model to use the correct format!

If you want to compare the value to the current date, you have to remember the timestamp. You seem to note that the time component is an issue by converting getdate() to a string with no time, but it is in the wrong format.

Once you have a date, simply use where checkDate = convert(date, getdate()).

Upvotes: 6

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use cast() :

where cast(checkDate as date) = cast(getdate() as date)

However, the primary suggestion is to change the data model to store dates as standard date format.

Upvotes: 1

Related Questions