//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
      COM / ActiveX Components
      Forms
      General Topics
      Date/Time Routines
      Email Scripts & Info
      Files/Directories & FSO
   Databases
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
classicasp.aspfaq.com

ASP FAQ Tutorials :: Classic ASP 1.0 :: Date/Time Routines :: Could I get a little help with dates?


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...

Related Articles

Can I get millisecond accuracy in ASP?
Can I make VBScript format dates for me?
Given a date, how do I find the beginning and end of that week?
Given two dates, how do I determine an age?
How do I calculate dates, such as the first day of the month?
How do I convert a DATEDIFF to days, hours, and minutes?
How do I convert a timespan, in seconds, to HH:MM:SS?
How do I convert local time to UTC (GMT) time?
How do I count the number of business days between two dates?
How do I delimit/format dates for database entry?
How do I determine the number of seconds since 1/1/1970?
How do I display time in military format?
How do I implement a calendar / datepicker in ASP?
How do I select time only from a DATETIME column?
Why do I have problems inserting NOW() into a database?
Why does JavaScript's document.lastModified() not work in ASP files?

 

 


Created: 7/9/2000 | Last Updated: 2/4/2003 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (357)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...