472,783 Members | 959 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,783 developers and data experts.

Literal DateTimes and Their Delimiters (#).

32,534 Expert Mod 16PB
The ANSI standards for SQL specify that literal dates should specifically be entered in m/d/y format. In this case (y) can refer to (yy) or (yyyy). This is completely independent of regional settings.

The delimiter character for dates, times or datetime combinations, is the hash (#). See (Quotes (') and Double-Quotes (") - Where and When to use them.) for more on literal delimiters

The Format() function in VBA can be used with these settings to provide the literal datetime required (NB I'm not aware of any lack of flexibility on the Time Format side so I merely include it to provide usable examples) :
Expand|Select|Wrap|Line Numbers
  1. Data Type (Example)             Format String     (Required Result)
  2. Date      (1 February 2003)     '\#m\/d\/yyyy\#'  (#2/1/2003#)
  3. Time      (6:15:42 PM)          '\#hh:n:s\#'      (#18:15:42#)
  4. Date + Time                     '\#m\/d\/yyyy hh:n:s\#'
  5.           (1 February 2003 6:15:42 PM)            (#2/1/2003 18:15:42#)
Common Problems
Like most interpreters of code in their various forms (VBA; SQL; etc), the MS Access Jet SQL interpreter can be clever (MS Access is notoriously forgiving) so will interpret your date literal correctly in many cases even when it is passed in the wrong format. 24 January 2005, for instance, would be worked out correctly in any format I have ever seen used.
Consider the date above though, 1 February 2003. It could be interpreted as 2 January 2003 if passed in British (d/m/yyyy) format or even as 3 February 2001 if passed in yy/m/d format. yyyy/m/d should always be safe though, if not quite standard.
Stick with the standard (m/d/y) if you require your code to be (more) portable though.

Related to the previous paragraph.
One thing it is very important to understand is that dates that are specified using a format other than the SQL standard (EG. A date of 13th February 2015 specified as #13/2/2015#.) will still be interpreted as a valid date if they can nevertheless be recognised. This can be a very hard situation to discover when testing and debugging code so be careful. It generally means the format used is incorrect but you weren't lucky enough to discover it during testing because it was between 13th and the end of the month.

Except in countries where the SQL format matches the local format, this will mean that a string will be interpreted one way when used in VBA, yet in another way when interpreted by SQL.

Regional Settings
Another, rather nasty, problem raises its head when dealing with Regional Settings in regions where the standard date format character (Slash (/) for many countries including USA & UK as well as various others in Europe) is replaced by any other character. Countries such as Denmark for instance, where their dates are in the format (d-m-y). This issue is really a problem with VBA and the Format() command specifically. A standard format string of "m/d/yyyy", when used by Format() is parsed and interpreted in light of the Regional Settings such that it is treated as if it were "m-d-yyyy" instead. This is clearly not good news when trying to produce a conformant string for a SQL command string. The way to get around this, and be fully portable internationally, is to use a backslash (\) before each slash (/) in the format string - as illustrated in the Formats section above. This ensures that the backslashes are not recognised as format characters, yet still end up in the same place in the returned string value.

Date Variables
Date variables do not need to be formatted when passed to SQL as a reference (as opposed to a literal string of characters within the SQL).
DateTime fields in a record, dates entered into parameters in a parameter query or any function returning a DateTime result will be correctly interpreted by the SQL engine IF IT IS INTERPRETED BY SQL and not pre-processed by Access into a date literal. In the latter case the format should be done in the standard fashion.

It's easy to get some of this stuff wrong so I always recommend doing a 'Debug.Print strSQL' before passing the string to the SQL engine when developing the code, or even where you know there is a problem with it somewhere. Use Ctrl-G from the VBA window to show and go to the Immediate Pane where the string is displayed.
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strSQL
  2. DoCmd.RunSQL strSQL
Allen Browne has some further help on this topic (International Dates in Access).
Dec 10 '06 #1
1 32511
32,534 Expert Mod 16PB
It's come to my attention recently that another ANSI SQL standard for dates exists which is largely at odds with this other one explained here. MSDN has a lot more on it (Using Date and Time Data), but much of it is not compatible with Jet SQL (used by Access).

The other, reliable and standard form of a date literal that should work anywhere is "y-m-d" (Be careful here with Jet as yy may be treated as a day value. Only yyyy is 100% reliable in all circumstances). Again, this can be mixed with a time (except not in the ANSI standard way in Jet) to create a Date/Time value. I'm pretty sure it's also language and location independent. Access/Jet will always require hashes around the literal values though. No matter which format is attempted.

Some examples :

The Format() function in VBA can be used with these settings to provide the literal datetime required (NB I'm not aware of any lack of flexibility on the Time Format side so I merely include it to provide usable examples) :
Expand|Select|Wrap|Line Numbers
  1. Data Type    (Example)             Format String       (Required Result)
  2. Date         (1 February 2003)     '\#yyyy\-m\-d#'     (#2003-2-1#)
  3. Time         (6:08:42 PM)          '\#HH:nn:ss\#'      (#18:08:42#)
  4. Date + Time                        '\#yyyy\-m\-d HH:nn:ss\#'
  5.              (1 February 2003 6:08:42 PM)              (#2003-2-1 18:08:42#)
PS. I amended the format strings where the character '-' is used to avoid the risk of these being misinterpreted in countries, like Denmark, where this is the recognised date separator. It's possible it's not necessary for full portability, but I expect it is.
Feb 5 '12 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: John | last post by:
Ok, I posted in here a few days ago about a problem with apostrophes in text fields and I tried a few of the suggestions and now I'm in so deep I looking at scraping the whole thing because now I...
by: Michael Pfeifer | last post by:
Hi all, I need to convert datetimes from any timezone to UTC and back considering daylight saving. It is not enough, to just add or subtract the timezone offset. Also, I can not use c functions...
by: Guadala Harry | last post by:
In an ASCX, I have a Literal control into which I inject a at runtime. litInjectedContent.Text = dataClass.GetHTMLSnippetFromDB(someID); This works great as long as the contains just...
by: Pieter Coucke | last post by:
Hi, For some reason, somewhere in my application 1 hour is added to my dates, depending in which time zone the application is run... Because I don't have a clue where this happens, I posted this...
by: Geometer | last post by:
Hello, and good whatever daytime is at your place.. please can somebody tell me, what the standard behavior of strtok shall be, if it encounters two or more consecutive delimiters like in...
by: m6s | last post by:
1. After hours of researching, I used these snippets : void Object::TokenizeLines(const string& str, vector<string>& tokens, const string& delimiters) // Skip delimiters at beginning....
by: bearophileHUGS | last post by:
I have just re-read the list of changes in Python 2.6, it's huge, there are tons of changes and improvements, I'm really impressed: http://docs.python.org/dev/whatsnew/2.6.html I'll need many...
by: zaimoni | last post by:
I've already calculated that the following are valid and should not error, as both just end up with the character literal 'A' being their control expression. The unspecified value of the char*...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.