Issues with data format

Question

Issues with data format

kk.86manu

Ten Centuries

Points: 1268
More actions
December 4, 2012 at 7:54 am

#265735

Hello All,
IF '12/04/2012' < '03/01/2013
select 1
The above query is not fetching any result but if i use '>' condition it is giving me 1.Why?
Kindly help

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Because you are comparing a string value against a string value and the first is not less than the second, you need to ensure you use the right data types.

declare @d1 date = '12/04/2012', @d2 date = '03/01/2013'

if @d1 < @d2

select 1

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Or even this.

IF cast('12/04/2012' as datetime) < cast('03/01/2013' as datetime)

select 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kk.86manu Ten Centuries Points: 1268 More actions · Answer 3

Thanks for your reply

I just have one more clarification

IF '12/07/2012'<'12/01/2013'

For the mentioned condition the first value is the same and second value is greater and again the condition should not satisfy

In this case how the comparison will occur.After the first string is compared will it compare the next 2 strings?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

kk.86manu (12/7/2012)
Thanks for your reply
I just have one more clarification
IF '12/07/2012'<'12/01/2013'
For the mentioned condition the first value is the same and second value is greater and again the condition should not satisfy
In this case how the comparison will occur.After the first string is compared will it compare the next 2 strings?

Are you looking at those string values as dates or strings? As soon, and as told earlier, SQL will look at those as strings unless you convert them to dates using the CONVERT function. As strings, '12/07/2012' < '12/01/2013' is false. If you sort them as strings it would look like this:

'12/01/2013'

'12/07/2012'