Could I get a little help with dates?

(1) Entering/comparing dates in a SQL Query 
 
You should try your best to use YYYYMMDD format wherever possible. Localized formats (mm/dd/yyyy, dd/mm/yyyy) only cause confusion and often lead to incorrect data. 
 
Make sure your date is a valid date. You can do this in VBScript using the isDate() function. Syntax is: 
 

<%      if isDate(dateVar) then          ' do something      else          ' do something else      end if  %>

 
A SQL query might look like this: 
 
NOTE: If you are using Access, you need to surround dates with pound signs (#). With most other databases, including SQL Server 7, dates are surrounded with apostrophes (') and are treated like strings. 
 

<%      ' *** SQL Server:      sql = "SELECT column FROM table WHERE dateCol > '" & dateVar & "'"        ' *** for Access:      ' sql = "SELECT column FROM table WHERE dateCol > #" & dateVar & "#"        ' *** for a stored procedure:      ' sql = "EXEC Proc_name @dateParam = '" & dateVar & "'"  %>

 
If you only want records with datefields in the last n days, you can do something like this: 
 

<%      n = 5        ' *** SQL Server:      sql = "SELECT column FROM table WHERE dateCol >= DATEADD(DAY, -" & n & ", GETDATE() - {fn CURRENT_TIME})"  %>

 
If you want records that fall between two dates (inclusive), you can do this: 
 

<%      sql = "SELECT column FROM table " & _      "WHERE dateCol >= '" & dateVar1 & _      "' AND dateCol < DATEADD(DAY, 1, '" & dateVar2 & "')"  %>

 
If you want records that are within the current month: 
 

<%      sql = "SELECT column FROM table " & _      "WHERE dateCol >= GETDATE()-{fn CURRENT_TIME}-DAY(GETDATE())+1      "' AND dateCol < DATEADD(MONTH, 1, GETDATE()-{fn CURRENT_TIME}-DAY(GETDATE())+1)"    ' or        sql = "SELECT column FROM table " & _      "WHERE MONTH(dateCol) = MONTH(GETDATE()) " & _      "AND YEAR(dateCol) = YEAR(GETDATE())"  %>

 
Another tip... don't name columns with reserved words like DATE or TIME. 
 
(2) Dealing with mm/dd/yyyy vs. d/m/yy vs. m/d/yy [...] 
 
Use YYYYMMDD format for all dates when passing to the database. Then the database won't care which way it's set up internally, the default locale (or the current user's regional settings) on the database machine, the default locale (or current user's regional settings) of the IIS machine passing dates through ASP, and the date that the user entered manually. Here is a quick example of converting ASP's date to YYYYMMDD format: 
 

<%       dateVar = year(date) &_          left("00",2-len(month(date))) & month(date) &_          left("00",2-len(day(date))) & day(date)   %>

 
Of course, it will be up to you that dates entered by the user are in the correct format. No code can determine whether the user who typed in 2/3/01 actually meant Febraury 3rd or March 2nd - it can only determine in which format the application developer expects entries to be made. 
 
See Article #2260 for more information on using YYYYMMDD format. 
 
(3) Comparing/determining dates 
 
VBScript has many useful date functions that can help you with many issues. One problem I had on a project was running a loop from the first day of the PREVIOUS month to today. The DateAdd() function helped with this immensely. 
 
First I moved back a month by adding -1 months to today's date: 
 

<%      lastmonththisday = dateadd("m",-1,date())  %>

 
Then I subtracted from that date the number of days that had passed that month (which would bring you back to the last day of the previous month), and added one: 
 

<%      lastmonthfirstday = dateadd("d",-day(date())+1,lastmonththisday)  %>

 
Now I put that together in a for loop that created a table, with each day from the beginning of last month to today (VBScript's for loop works with dates excellently): 
 

<%      response.write("<table>")      lmfd = dateadd("d",-day(date())+1,(dateadd("m",-1,date())))      for i = lmfd to date()          response.write("<tr><td>" & formatdatetime(i,1) & "</td></tr>")      next      response.write("</table>")  %>

 
I then threw in some logic to color the weekends with a different color: 
 

<%      response.write("<table>")      lmfd = dateadd("d",-day(date())+1,(dateadd("m",-1,date())))      for i = lmfd to date()          bg = "#ffffff"          if weekday(i)=1 or weekday(i)=7 then bg = "#ffffcc"          response.write("<tr><td bgcolor=" & bg & ">" & formatdatetime(i,1) & "</td></tr>")      next      response.write("</table>")  %>

 
There is a more comprehensive date tutorial at learnASP.com: 
 
    http://www.learnasp.com/learn/datetime.a... 
 
If you are using J(ava)Script, see the following index at Merlyn: 
 
    http://www.merlyn.demon.co.uk/js-dates.h...