How to get data between two dates where the date column is varchar in SQL Server

Question

I have a table in SQL Server 2008 with a MessageTimestamp column of type varchar(50) instead of datetime. I would like to get data from this table between two dates.

Normally, if the column was datetime, I would use a query like:

select * 
from myTable
where MessageTimestamp >= @firstDate and MessageTimestamp <= @lastDate

However, I cannot use this right now since this is varchar. I need a way as easy as possible to get those data rather than updating the column type. Any suggestion would be appreciated.

Edit: example date format stored -> 2016-05-16 11:20:00 AM

You need to use Convert() to convert your varchar to a datetime — Daniel
– Daniel, Commented Aug 31, 2016 at 19:10
Why are you using the wrong data type? This is like asking how to clean the floor with a toothbrush rather than buying a mop. — Aaron Bertrand
– Aaron Bertrand, Commented Aug 31, 2016 at 19:29

Ola Ekdahl · Accepted Answer · 2016-08-31 19:16:20Z

1

Use the CONVERT or CAST function. CONVERT(datetime, MesssageTimestamp) for example.

edited Aug 31, 2016 at 19:16

answered Aug 31, 2016 at 19:11

Ola Ekdahl

1,5141 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Juan Garcia Over a year ago

Based on the previous answer you need to convert the column convert(datetime, MessageTimestamp) >= @firstdate

Ola Ekdahl Over a year ago

That is correct I updated my answer. It's the column containing the varchar data that needs to be converted in order to be compared to time data types.

Eray Balkanli Over a year ago

Thanks this is working, just one thing, for another table I see the date is saved as "19/05/2016 11:52:03 AM" and casting is not working for it. Any suggestion?

Eray Balkanli Over a year ago

just a note: convert(datetime,@date,103) worked for the other. thanks

S3S Over a year ago

@ErayBalkanli it should work with convert. SELECT convert(datetime, '19/05/2016 11:52:03 AM', 103)

Unnikrishnan R · Accepted Answer · 2016-08-31 19:14:12Z

1

Try with the below code.

    Select * from myTable
     where cast(MessageTimestamp as datetime )>= @firstDate and cast(MessageTimestamp as datetime) <= @lastDate

answered Aug 31, 2016 at 19:14

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to get data between two dates where the date column is varchar in SQL Server

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related