Issues with data format

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Issues with data format

Issues with data format

Rate Topic

Display Mode

Topic Options

Author

Message

kk.86manu

Posted Tuesday, December 04, 2012 7:54 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 3:32 AM
Points: 38, Visits: 167

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

anthony.green

Posted Tuesday, December 04, 2012 8:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Sean Lange

Posted Tuesday, December 04, 2012 8:16 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 8,620, Visits: 8,261

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

Post #1392523

kk.86manu

Posted Friday, December 07, 2012 1:37 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 3:32 AM
Points: 38, Visits: 167

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

Lynn Pettis

Posted Friday, December 07, 2012 3:15 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 21,624, Visits: 27,465

kk.86manu (12/7/2012)

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'

Cool

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 »

Permissions