CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SpringLearner
SpringLearner

Reputation: 13844

varchar vs datetime in sql

I was having problem in retrieving from SQL Server so I posted this [question][1]

I did not get any suitable answers. So I have changed the column datatype from datetime to varchar and now it works fine.

SELECT *  
FROM test
WHERE (timeStamp BETWEEN '05-09-2013 18:23:57' AND '05-09-2013 18:23:59')

But my query if varchar datatype can play the role of datetime and in varchar we can also store the string then why sql provides datetime datatype? I know varchar occupies more space than datetime. I would like to know other reasons.

Upvotes: 1

Views: 2854

Answers (3)

Madhivanan
Madhivanan

Reputation: 13700

Always use proper DATETIME datatype to store date and time values. Refer this for more information http://beyondrelational.com/modules/2/blogs/70/posts/10902/understanding-datetime-column-part-iv.aspx

Upvotes: 1

roman
roman

Reputation: 117370

Change datatype of your column to datetime. You can do your query IF you'll use datetime instead of varchar in where clause:

select *
from test
where timeStamp between convert(datetime, '2013-09-05 18:23:57', 120) and convert(datetime, '2013-09-05 18:23:59', 120)

I'm pretty sure it would work even with implicit cast if you use ISO format of date:

select *
from test
where timeStamp between '2013-09-05 18:23:57' and '2013-09-05 18:23:59'

Here's more info about cast and convert.

Upvotes: 4

user2763765
user2763765

Reputation:

Another reason apart from space is this:

Datetime has other functions like picking up the day, year, month,hours,minutes,seconds etc so that you don't have to write it for yourself. If you use varchar then it will be your responsibility to provide functions for future use. You should use split function to retrive the part of date you want.

Another is that a query on a varchar works slower when compared to Datetime when you use to conditions to compare month / day/ year

Upvotes: 2

Related Questions