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:
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.
If you only want records with datefields in the last n days, you can do something like this:
If you want records that fall between two dates (inclusive), you can do this:
If you want records that are within the current month:
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:
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:
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:
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):
I then threw in some logic to color the weekends with a different color:
There is a more comprehensive date tutorial at learnASP.com:
If you are using J(ava)Script, see the following index at Merlyn:
Related ArticlesCan 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?