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 |
|
chwanhun
Starting Member
22 Posts |
 Posted - 2012-03-01 : 13:17:38
|
| Hello!Why does this not return dates in 2012?ISNULL(CONVERT(VARCHAR(10),closeddate,101), '12/31/9999') >= '12/31/2011'However these conditions return the correct results...ISNULL(CONVERT(VARCHAR(10),Alloc.ct_closeddate,101), '12/31/9999') <> '12/31/9999'ISNULL(CONVERT(VARCHAR(10),Alloc.ct_closeddate,110), '12319999') = '12/31/2011'ISNULL(Alloc.ct_closeddate,'12/31/9999') >= '12/31/2011'Why?Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-01 : 13:56:20
|
| why are you making dates as varchar before comparison?why not usecloseddate>='2011-12-31'?whenever you make it varchar it will do string comparison which can cause output to come incorrectly------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
chwanhun
Starting Member
22 Posts |
Posted - 2012-03-01 : 14:33:11
|
| Thanks.I was doing that because I had other comparison problems when I didn't use CONVERT. The SQL would not return records with dates of 12/31/2011 04:00:00:000 if I were using ISNULL(closeddate,'12/31/9999') <= '12/31/2011' because of the hour. That's why I decided to only compare using the date portion of the datetime. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-03-01 : 14:44:48
|
ISNULL(ClosedDate, '99991231') < '20120101'
N 56°04'39.26"E 12°55'05.63" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-01 : 23:11:15
|
quote:
Originally posted by chwanhun
Thanks.I was doing that because I had other comparison problems when I didn't use CONVERT. The SQL would not return records with dates of 12/31/2011 04:00:00:000 if I were using ISNULL(closeddate,'12/31/9999') <= '12/31/2011' because of the hour. That's why I decided to only compare using the date portion of the datetime.
it will return if you're using a interval using >= and <------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Datetime comparison