Comparing datetime variable with varchar variable

Question

Comparing datetime variable with varchar variable

sachinrshetty

SSCrazy

Points: 2062
More actions
April 19, 2010 at 11:08 pm

#220628

Hi,
I have datetime variable in mm/dd/yyyy format. I have table where the field value contains date in mm/dd/yyyy format but its data type is varchar.
Same format but as different datatypes.
If i compare those two variables am i going to get correct results?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

datetimes are not stored in that format, it just happens to be the default way your regional settings are presenting the datetime. they are actually stored in numeric formats behind the scenes.

always, i mean always compare datetime to datetime...so that means if you have a varchar, convert or cast it to datetime

SELECT CONVERT(datetime,'04/10/2010')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sachinrshetty SSCrazy Points: 2062 More actions · Answer 2

Thank u Lowell for your answer but i have to pass a date value to this stored procedure from front end.

So in which shall i pass either in mm/dd/yyy or yyyy/mm/dd.

Which one will be better for operations inside stored procedure

krayknot SSCertifiable Points: 7998 More actions · Answer 3

sachinrshetty (4/20/2010)
Thank u Lowell for your answer but i have to pass a date value to this stored procedure from front end.
So in which shall i pass either in mm/dd/yyy or yyyy/mm/dd.
Which one will be better for operations inside stored procedure

from the frontend pass the string Date value but in date format.

inside th stored procedure convert the string date into sql datetime format

like

convert(datetime, <date>)

or,

convert both in string like

Convert(varchar, convert(datetime, <date>),103)

now you can compare.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

sachinrshetty SSCrazy Points: 2062 More actions · Answer 4

Really good suggestion will definitely work.

Thank you very much

kshitij kumar

Paul White SSC Guru Points: 150467 More actions · Answer 5

sachinrshetty (4/20/2010)
Thank u Lowell for your answer but i have to pass a date value to this stored procedure from front end.

What is the front end? Many languages enable you to call a stored procedure with proper types - not just strings.

Lowell's advice was the best so far in this thread - create, pass, and process dates as dates 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi