Gustav Brock
Gustav at cactus.dk
Fri Apr 1 12:37:43 CDT 2011
Hi Rocky et al
After all these years it's a surprise to me how much uncertainty, hype, and misunderstandings there seems to be about how to handle dates and SQL.
It isn't that difficult and no magic is involved, so please read carefully.
The rules are simple but strict and should be followed at all times. No exceptions as there do not exist cases where the rules fall short.
First, one has to distinguish between a "date/time value" (of data type Date) and a "string expression for a date/time value" (of data type String).
The use of any other data type like Double (the underlying data type of Date) or Long is never needed or may even fail (Long!).
In VB(A) data type Date is always what to use:
Dim datSomeDateTime As Date
datSomeDateTime = Now
The only exception is Variant which can be used as (a temporary) data type if Null must be an acceptable value.
Dim datSomeDateTime As Date
Dim varSomeDateTime As Variant
varSomeDateTime = rst!Fields("SomeDateTime")
If Not IsNull(varSomeDateTime) Then
datSomeDateTime = varSomeDateTime
End If
String expressions for date/time values are needed only for display of formatted values and for building SQL strings.
A. Formatted values are used for:
1. A TextBox if you for some reason prefer not to set the Format property of this (clear the Format property):
Me!txtSomeDateTime = Format(Now, "mmm-dd")
or used as ControlSource:
=Format(Now, "mmm-dd")
2. A recordsource for a Combo/ListBox (where any field not text _will_ be casted to text out of your control):
Select *, Format([SomeDateTime], "mmm-dd") From tblSomeTable
3. Some other purposes like export to a text file.
B. SQL strings are used in many occasions, mostly for creating dynamic SQL queries.
Problem here is that different SQL engines as well as ADO take different formats and delimiters for date/time values.
In SQL, date/time values can origin from three sources:
1. Fields of data type Date
2. Functions that return data type Date
3. String expressions
Whenever a string expression for a date/time value is needed, the date/time value must be converted by you to a string of a defined format to play safe; if you don't, VB(A) will itself cast the date/time value to text using the local settings of Windows, which means you are out of control and your code will fail sooner or later.
As to which format to use, it is all about avoiding any possible trap.
Here are some typical formats and why not to use them:
dd/mm/yyyy - will fail in most cases
mm/dd/yyyy - accepted by most engines but will fail in ADO (FindFirst)
yyyymmdd - will fail for some engines and anywhere in Access
dd-mmm-yy and any other format using literal months - will fail in non-English environments
That leaves this single unambiguous format - the ISO format - which will never fail:
yyyy/mm/dd (or yyyy-mm-dd, doesn't normally matter)
Further, and maybe for the reason to be safe, this _is_ the preferred and recommended format for SQL Server.
Thus, there really is no reason to use any other format than this.
So, some examples:
1.
strSQL = "Select tblA.*, tblB.* From tblA, tblB Where A.[SomeDateField]=B.[SomeDateField];"
2.
strSQL = "Select tblA.* From tblA, tblB Where A.[SomeDateField]=DateAdd('d',-8,B.[SomeDateField]);"
strSQL = "Select tblA.* From tblA, tblB Where A.[SomeDateField]=DateSerial(" & intYear & "," & intMonth & "," & intDay & ");"
3.
strDate = Format(DateAdd("d", -8, Date), "yyyy\/mm\/dd")
For Access SQL:
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]=#" & strDate & "#";
For SQL Server and many other engines:
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]='" & strDate & "'";
One important additional note is about fetching values from a TextBox. If this is either bound to a field of data type Date or has been applied a Format property as a date, it will return a value of data type Date.
Further, a typed in date _will_ be read according to the local settings of Windows even if these differ from the Format property setting. Thus, the Format property setting has no influence of the value returned. It is only when the TextBox has focus and if you then read the Text property of the TextBox you will have the displayed string returned.
This means that the value of such a TextBox can safely (except for Null) be passed to a date/time variable:
datSomDateValue = Forms!frmSomeForm!txtSomeDate
or to be explicit:
datSomDateValue = Forms!frmSomeForm!txtSomeDate.Value
This also means that this will never fail (except for Null values or if the form is closed):
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= Forms.frmSomeForm.txtSomeDate";
However, this may fail because the date value is casted according to the local settings:
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= #" & Forms!frmSomeForm!txtSomeDate & "#";
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= #" & datSomeDateValue & "#";
This will not fail in Access SQL (for most other engines replace # with single quote):
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= #" & Format(Forms!frmSomeForm!txtSomeDate, "yyyy\/mm\/dd") & "#";
But this may fail (because the date/time values will be casted to text out of your control):
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= DateValue(" & Forms!frmSomeForm!txtSomeDate & ")";
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]=#" & DateValue(Forms!frmSomeForm!txtSomeDate) & "#";
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= CDate(" & Forms!frmSomeForm!txtSomeDate & ")";
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]=#" & CDate(Forms!frmSomeForm!txtSomeDate) & "#";
It also means that - regarding the topic - this doesn't make sense because Forms.frmSomeForm.txtSomeDate already is of data type Date:
datSomeDateTime = DateValue(Forms!frmSomeForm!txtSomeDate)
What happens is that first Forms.frmSomeForm.txtSomeDate is casted to a string - because that is what DateValue expects - then casted by DateValue to data type Date. Back and forth. What you will obtain, however, by this method is that a possible time part of Forms!frmSomeForm!txtSomeDate will be stripped.
This makes no sense at all as it converts a date/time to a date/time:
datSomeDateTime = CDate(Forms!frmSomeForm!txtSomeDate)
The only reason for using:
datSomeDateTime = DateValue(Forms!frmSomeForm!txtSomeDate)
would be the rare case where the TextBox is neither bound to a date/time field nor formatted as a date/time - thus returning a value of data type Text - allowing the user to input a date as text in free style.
Happy Dating!
/gustav
>>> rockysmolin at bchacc.com 31-03-2011 19:55 >>>
Dear List:
I create a temp table using a SQL statement I construct in code to
incorporate various filter - one of which is a date filter.
Works well here but when I send it to the user in Bahrain who's regional
setting is (I assume from the screen shot he sent) English U.K. where the
date format is dd/mm/yyyy it fails. I set my regional settings on my box to
U.K. and sure enough it fails - returns all records with no date filtering.
Here's the SQL statement that creates the table:
INSERT INTO tblKittingToMIS ( PartNumber, OrderNumber, DueDate, QuantityDue
) SELECT tblDemand.PartNumber, tblDemand.OrderNumber, tblDemand.DueDate,
tblDemand.QuantityDue FROM tblDemand WHERE DueDate >= #31/03/2011# AND
DueDate <= #07/04/2011#
which is supposed to filter out any record in tblDemand where the Due Date
is outside of the range March 31 to April 7. But it don't.
When I look in tblDemand, the dates are displayed properly as dd/mm/yyyy.
Why doesn't this work? Should I be using some kind of different syntax for
this filter?
MTIA
Rocky Smolin
Beach Access Software
858-259-4334
Skype: rocky.smolin
www.e-z-mrp.com <http://www.e-z-mrp.com/>
www.bchacc.com <http://www.bchacc.com/>