Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issues with data format Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 7:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:47 AM
Points: 50, Visits: 222
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
Post #1392503
Posted Tuesday, December 4, 2012 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 5,221, Visits: 5,116
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1392518
Posted Tuesday, December 4, 2012 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,093, Visits: 12,574
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1392523
Posted Friday, December 7, 2012 1:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:47 AM
Points: 50, Visits: 222
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?
Post #1393913
Posted Friday, December 7, 2012 3:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:52 PM
Points: 20,755, Visits: 32,573
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'




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1393959
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse