Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2012-04-26 : 11:21:54
|
| There is something wrong with these queries I think.Can you see what I am doing wroong please?ThanksLet's say today's date is 26 April 2012Query number 1 should return 1 row whereas it does notQuery number 2 should NOT return any row whereas it does1-SELECT Field1, Field2, Field3 FROM tblMain WHERE Field1 = 'xxx' AND ( '26/04/2012' < ('03/05/2012'))2-SELECT Field1, Field2, Field3 FROM tblMain WHERE Field1 = 'xxx' AND ( '26/04/2012' > ('03/05/2012')) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-26 : 11:46:26
|
It is doing a string comparison because SQL doesn't know that you want it to be converted to dates. Cast at least one side to date/datetime. Also, preferably, use unambiguous date formatting (YYYYMMDD).AND (CAST('20120426' AS DATETIME) < '20120503') |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-26 : 12:40:16
|
| i dont really understand the relevance of those constant date value comparisonDo they come from a variable in actual query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-26 : 12:49:11
|
quote:
Originally posted by visakh16
i dont really understand the relevance of those constant date value comparisonDo they come from a variable in actual query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Might be experimenting to see if the logic evaluates to TRUE/FALSE as proof of concept. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2012-04-26 : 13:08:46
|
| Can you see what is wrong with this query please?It says incorrect syntx next ) which is pointing to the last )Thanksset @criteriaOR = replace(@criteriaOR, 'tradedate', 'convert(datetime, ' + char(39) + convert(varchar(11), getdate(), 103) + char(39) + ')' + ) |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-04-26 : 13:15:42
|
| remove the + just before the last ) if that last ) goes with REPLACE<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
|
|
|
|
|
date issue