Date comparision issue Help !! Urgent

• SD-155976

  SSC Rookie

  Points: 37

  More actions

  April 20, 2006 at 7:39 am

  #91631

  Has anyone encountered below mentioned

  I have following data in a table1

  FromDate

  2006-03-08 14:38:38.387

  2006-03-09 07:13:28.800

  The value that I want to compare it with datetime valriable @attendancedate = '03/09/2006'

  When I do a

  1)

  Select max(fromdate) from table1

  where fromdate <= attendancedate

  It returns:  2006-03-08 14:38:38.387

  2) Select max(fromdate) from table1

  where fromdate <= attendancedate +   ' 23:59:59.000' 

  It returns: 2006-03-09 07:13:28.800

  3) Select max(fromdate) from table1 

  where CONVERT(CHAR(10),dtstatusChangeFrom,110)   <= @dtAttendanceDate 

  It returns : 2006-03-09 07:13:28.800

   

  I should get the return value as 2006-03-09 07:13:28.800, Is the 2) or 3) the correct way to do the same

  Let me know if you need more information.

  Thanks in Advance,

  SD

• paul_richardson

  SSCarpal Tunnel

  Points: 4594

  More actions

  April 20, 2006 at 8:36 am

  #633284

  hi,  i think that if you don't specify a time 00:00:00 is appended to it, that's why in case 1) the 8th March row is returned. 

  another solution would be to use dataadd

  Select max(fromdate) from table1

  where fromdate <= dateadd(day,1,attendancedate)

  this then compares this to '03/10/2006 00:00:00'

  hope this helps

  paul 

• SD-155976

  SSC Rookie

  Points: 37

  More actions

  April 20, 2006 at 9:22 am

  #633301

  Thanks paul!!  that helped, as I will be comapring date datatype with datetime variable.

  -SD

   

• SD-155976

  SSC Rookie

  Points: 37

  More actions

  April 20, 2006 at 12:30 pm

  #633352

  In the above solution th e "<=" by adding one more day we will not be able to use the "=" sign. Do you think this might work:

  Select max(fromdate) from table1

  where Convert(char(10),fromdate,101) <= @attendancedate

  Thanks,

  SD.

• Luke L

  SSC-Dedicated

  Points: 35547

  More actions

  April 20, 2006 at 2:19 pm

  #633371

  If I understand your question correctly, you're looking to compare just the date portion of FromDate not the time portion?  If so the easiest and fastest way I've seen to do this is be dropping the time down to 0:00:00.  So you'd have...

  Select max(fromdate) from table1

  where DATEADD(dd, DATEDIFF(dd,0,fromdate), 0) <= @attendancedate

  The how's and whys the DATEADD/DATEDIFF works can be found here...

  -Luke.

  To help us help you read this[/url]For better help with performance problems please read this[/url]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 21, 2006 at 1:48 am

  #633421

  Easiest, yes, fastest, no.

  Using a function on a column in the where clause of a query will prevent the optimiser from using any index that may exist on that column, forcing a full table/clustered index scan.

  The dateadd and datediff will work, please just note that if the table is large, it will not be quick.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Stewart Joslyn

  SSCertifiable

  Points: 6131

  More actions

  April 21, 2006 at 6:08 am

  #633477

  For performance, create a calculated column with just the date part and index that.

• Jeff Moden

  SSC Guru

  Points: 1004366

  More actions

  April 22, 2006 at 9:53 pm

  #633673

  In this case, I think (small modification of) Paul's solution is going to be the fastest if there is an index on the FromDate column... just remove the "=" sign to include all of the 9th but not the 10th... that way, there is no formula on a column in the WHERE clause...

  Select max(fromdate) from table1

  where fromdate < dateadd(day,1,@attendancedate)

  Otherwise, if you need to do something like grouping by whole day, Stewart's solution of using an indexed calculated column that references FromDate will be the fastest.  And, the fastest formula for correctly stripping time without inherent rounding errors is...

  CONVERT(DATETIME,CONVERT(INT,FromDate-.5))

  ...but don't take my word for it... here's the results of a million row test with DBCC commands the clear the cache before each test...

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Results from the CONVERT/VARCHAR method...

        5763 :Total server duration (ms)

        5469 :CPU Usage (ms)

         131 :Total Disk Reads/Writes

  ==============================================================================

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Results from the CAST/FLOOR method...

        2080 :Total server duration (ms)

        1781 :CPU Usage (ms)

         131 :Total Disk Reads/Writes

  ==============================================================================

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Results from the DATEADD/DATEDIFF method...

        1220 :Total server duration (ms)

         797 :CPU Usage (ms)

         131 :Total Disk Reads/Writes

  ==============================================================================

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Results from the CONVERT/INT method...

        1093 :Total server duration (ms)

         641 :CPU Usage (ms)

         131 :Total Disk Reads/Writes

  ==============================================================================

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Results from the CALCULATED COLUMN method...

        1843 :Total server duration (ms)

        1703 :CPU Usage (ms)

         380 :Total Disk Reads/Writes

  ==============================================================================

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Results from the FUNCTION method...

        8970 :Total server duration (ms)

        8687 :CPU Usage (ms)

         137 :Total Disk Reads/Writes

  ==============================================================================

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  April 24, 2006 at 11:04 am

  #633867

  Hi all,

  For my own amusement, I took a look at some end points for a few of the methods...

  --data

  declare @t table (d datetime)

  insert @t

            select '20060424 23:59:59.990'

  union all select '20060424 23:59:59.991'

  union all select '20060424 23:59:59.992'

  union all select '20060424 23:59:59.993'

  union all select '20060424 23:59:59.994'

  union all select '20060424 23:59:59.995'

  union all select '20060424 23:59:59.996'

  union all select '20060424 23:59:59.997'

  union all select '20060424 23:59:59.998'

  union all select '20060424 23:59:59.999'

  union all select '20060425 00:00:00.000'

  union all select '20060425 00:00:00.001'

  union all select '20060425 00:00:00.002'

  union all select '20060425 00:00:00.003'

  union all select '20060425 23:59:59.990'

  union all select '20060425 23:59:59.991'

  union all select '20060425 23:59:59.992'

  union all select '20060425 23:59:59.993'

  union all select '20060425 23:59:59.994'

  union all select '20060425 23:59:59.995'

  union all select '20060425 23:59:59.996'

  union all select '20060425 23:59:59.997'

  union all select '20060425 23:59:59.998'

  union all select '20060425 23:59:59.999'

  union all select '20060426 00:00:00.000'

  union all select '20060426 00:00:00.001'

  union all select '20060426 00:00:00.002'

  union all select '20060426 00:00:00.003'

  --calculation

  select

      d as 'StoredDate',

      CONVERT(DATETIME,CONVERT(INT, d-.5)) as 'CONVERT/INT method',

      DATEADD(dd, DATEDIFF(dd,0,d), 0) 'DATEADD/DATEDIFF method',

      Convert(char(10), d,101) as 'CONVERT/VARCHAR method'

  from @t

  Jeff - There seems to be a minor issue with the 'CONVERT/INT method'. Have you come across this before? Makes me wonder if CONVERT(DATETIME,CONVERT(INT, d-.50000005)) might be the "best" method...

   

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• Jeff Moden

  SSC Guru

  Points: 1004366

  More actions

  April 24, 2006 at 6:07 pm

  #633919

  Yep...I see what you mean, Ryan... I was introduced to the CHAR/INT-.5 method a couple of days ago and (screwed up is what I did ) I didn't check all the possibilites.  The DATEADD/DATEDIFF has never failed in such a manner.  Thanks for taking the time to do the test.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 28, 2006 at 12:08 am

  #634750

  I tend to use this one. Not sure if it's the best, but it is way better than the usual cast as varchar version. Haven't seen any strange issues

  SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass