date - getdate() question

Results 1 to 3 of 3

Thread: date - getdate() question

  1. #1
    Join Date
    Dec 1969

    Default date - getdate() question

    Hello,<BR><BR>is there an easy way to compare a date in a database to the current date?<BR><BR>select id from stats where date=getdate()<BR><BR>doesn&#039;t work (since the timestamp if I recall right)<BR><BR>so<BR><BR>select id from stats where month(date)=month(getdate()) and day(date)=day(getdate()) and year(date)=year(getdate())<BR><BR>is the only way to handle this? (using MSSQL)

  2. #2
    Join Date
    Dec 1969

    Default RE: date - getdate() question

    You should probably look at the DatePart() or possibly DateDiff() functions - since it&#039;d be difficult to get two timstamps to match exactly with the degree of precision MS SQL makes available. all this is covered in Books Online.<BR><BR><BR>j<BR>

  3. #3
    Join Date
    Dec 1969

    Default Your way not bad, and...

    ...probably just as efficient as this way:<BR><BR> WHERE CONVERT(VARCHAR,dbdate,102) = CONVERT(VARCHAR,getDate(),102)<BR><BR>which is kind of the "standard" way to do it.<BR><BR>*HOWEVER*...<BR><BR>If you want more efficiency than you can get from SQL alone, then it&#039;s better to pass in two values from VBScript code:<BR><BR>SQL = "... WHERE &#039;" & Date() & "&#039; &#060;= dbdate AND dbdate &#060; &#039;" & (Date()+1) & "&#039;"<BR><BR>This is especially true if your dbdate field in the DB is indexed! Reason: When you invoke a function on a db field, you lose a lot of the advantage of the index. The query engine has to test *each and every value* (from the record or from the index, but still every value) of that field to see it if qualifies.<BR><BR>If you pass something like:<BR> <BR> SELECT ... WHERE &#039;9/12/2003&#039; &#060;= dbdate AND dbdate &#060; &#039;9/13/2003&#039;<BR><BR>then the query engine can turn that into an incredibly effiecient index-based query that almost instantly gets all the correct records.<BR><BR>Not how the WHERE is structured: You are asking for values for dbdate that match today&#039;s date and have any time value from 0:00:00 to 23:59:59. The use of &#060;= and &#060; is carefully chosen; don&#039;t change them of mix them up.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts