By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,475 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Workdays function with Australian Dates

P: n/a
I am trying to calculate the number of workdays between two dates with
regards to holidays as well. I have used Arvin Meyer's code on the Access
Web, but as I am in Australia and my date format is dd/mm/yyyy, I have found
that the dates I put in my holidays table are reversed into American dates.
So, the wrong holiday dates are subtracted from the total workdays between
the start and end dates. Is there an easy fix for this?

******Code follows******

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'................................................. ...................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'................................................. ...................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function******End Code******
Mar 15 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Format you date so -

Format(StartDate, "mm/dd/yyyy")

wherever you use a date in a query for comparison, and add a day so

DateAdd("d",1, StartDate)

This should help.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

"Dixie" <di***@dogmail.com> wrote in message
news:12************@corp.supernews.com...
I am trying to calculate the number of workdays between two dates with
regards to holidays as well. I have used Arvin Meyer's code on the Access
Web, but as I am in Australia and my date format is dd/mm/yyyy, I have
found that the dates I put in my holidays table are reversed into American
dates. So, the wrong holiday dates are subtracted from the total workdays
between the start and end dates. Is there an easy fix for this?

******Code follows******

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'................................................. ...................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'................................................. ...................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function******End Code******

Mar 15 '06 #2

P: n/a
It is the holiday dates in the table that are causing the problem. They are
not used in a query.

The problem is that if 11th March 2006 is a holiday, in my table of
holidays, I have 11/03/2006. When the code runs that deducts the holidays
from the list of workdays, it interprets this date as 3rd November 2006 -
3/11/2006.

I guess I could enter all the holiday dates in American format, but this
will inevitably cause problems when others try to enter them as they are not
used to dates in the American format.

dixie

"Jeff" <je************@asken.com.au> wrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
Format you date so -

Format(StartDate, "mm/dd/yyyy")

wherever you use a date in a query for comparison, and add a day so

DateAdd("d",1, StartDate)

This should help.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

"Dixie" <di***@dogmail.com> wrote in message
news:12************@corp.supernews.com...
I am trying to calculate the number of workdays between two dates with
regards to holidays as well. I have used Arvin Meyer's code on the Access
Web, but as I am in Australia and my date format is dd/mm/yyyy, I have
found that the dates I put in my holidays table are reversed into American
dates. So, the wrong holiday dates are subtracted from the total workdays
between the start and end dates. Is there an easy fix for this?

******Code follows******

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'................................................. ...................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'................................................. ...................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function******End Code******


Mar 15 '06 #3

P: n/a
Your system Regional settings should determine the form in which dates are
interpreted. I haven't looked at Arvin's code, but it sounds as if some date
is in _string_ form.

There is only one Date/Time field and it is not even in month/day/year form,
but stored as the number of seconds since a particular starting date (Dec.
30, 1899, if you must know). The Regional settings only apply to input and
display of the date/time data. Obviously, if you have dates in string form
in code, the Regional settings aren't going to affect them.

Larry Linson
Microsoft Access MVP

"Dixie" <di***@dogmail.com> wrote in message
news:12*************@corp.supernews.com...
It is the holiday dates in the table that are causing the problem. They
are not used in a query.

The problem is that if 11th March 2006 is a holiday, in my table of
holidays, I have 11/03/2006. When the code runs that deducts the holidays
from the list of workdays, it interprets this date as 3rd November 2006 -
3/11/2006.

I guess I could enter all the holiday dates in American format, but this
will inevitably cause problems when others try to enter them as they are
not used to dates in the American format.

dixie

"Jeff" <je************@asken.com.au> wrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
Format you date so -

Format(StartDate, "mm/dd/yyyy")

wherever you use a date in a query for comparison, and add a day so

DateAdd("d",1, StartDate)

This should help.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

"Dixie" <di***@dogmail.com> wrote in message
news:12************@corp.supernews.com...
I am trying to calculate the number of workdays between two dates with
regards to holidays as well. I have used Arvin Meyer's code on the
Access Web, but as I am in Australia and my date format is dd/mm/yyyy, I
have found that the dates I put in my holidays table are reversed into
American dates. So, the wrong holiday dates are subtracted from the total
workdays between the start and end dates. Is there an easy fix for this?

******Code follows******

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'................................................. ...................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'................................................. ...................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function******End Code******



Mar 16 '06 #4

P: n/a
Dates are Dates; there are no Australian dates, or American dates
(although Americans invented the notion of dates, along with everything
else). There are just dates.

This line
rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
specifies a string for a DAO recordset FindFirst.
How does VBA coerce StartDate to be part of the string?
TTBOMK it uses your Regional ShortDate settings.
So the Find String becomes "[HolidayDate] = #dd/mm/yyyy#"
(But for me it gives "[HolidayDate] = #yyyy-mm-dd#" which all
technologies I have used understand).

I predict the line will work correctly if it is changed to
rst.FindFirst "[HolidayDate] = " & Format(StartDate,"\#mm\/dd\/yyyy\#")
For StartDate being today, this will result in:
[HolidayDate] = #03/16/2006#

When using the string structure #Date# one must always be aware of this
possible problem. Perhaps it is used in ohter places in your work on
this particular issue.

I keep a function around in my module of general functions [in every
technology] to make it easy not to get burned. In VBA it looks like
this:

Public Function SQLDateString(ByVal d As Date) As String
SQLDateString = Format(d, "\#mm\/dd\/yyyy\#")
End Function

Mar 16 '06 #5

P: n/a
I am living in Holland. We also use dd/mm/yyyy
So I *always* use the lngvalue of dates. ==>>Never have any problems again.
I did have similar issues indeed...

So StartDate would become Clng(StartDate) and so on.

HTH
Arno R
"Dixie" <di***@dogmail.com> schreef in bericht news:12*************@corp.supernews.com...
It is the holiday dates in the table that are causing the problem. They are
not used in a query.

The problem is that if 11th March 2006 is a holiday, in my table of
holidays, I have 11/03/2006. When the code runs that deducts the holidays
from the list of workdays, it interprets this date as 3rd November 2006 -
3/11/2006.

I guess I could enter all the holiday dates in American format, but this
will inevitably cause problems when others try to enter them as they are not
used to dates in the American format.

dixie

"Jeff" <je************@asken.com.au> wrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
Format you date so -

Format(StartDate, "mm/dd/yyyy")

wherever you use a date in a query for comparison, and add a day so

DateAdd("d",1, StartDate)

This should help.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

"Dixie" <di***@dogmail.com> wrote in message
news:12************@corp.supernews.com...
I am trying to calculate the number of workdays between two dates with
regards to holidays as well. I have used Arvin Meyer's code on the Access
Web, but as I am in Australia and my date format is dd/mm/yyyy, I have
found that the dates I put in my holidays table are reversed into American
dates. So, the wrong holiday dates are subtracted from the total workdays
between the start and end dates. Is there an easy fix for this?

******Code follows******

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'................................................. ...................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'................................................. ...................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function******End Code******




Mar 16 '06 #6

P: n/a
Arno R wrote in message <44***********************@text.nova.planet.nl>
:
So StartDate would become Clng(StartDate) and so on.


Arno, have you tried that number trick on SQL server using ADO?

My guess would be that there would be a difference of a couple of days
when executing something like that on a separate connection to the
server vs on linked tables.

Then, what about upsizing your db to other platforms ...?

--
Roy-Vidar
Mar 16 '06 #7

P: n/a

"RoyVidar" <ro*************@yahoo.no> schreef in bericht news:mn***********************@yahoo.no...
Arno R wrote in message <44***********************@text.nova.planet.nl>
:
So StartDate would become Clng(StartDate) and so on.


Arno, have you tried that number trick on SQL server using ADO?

My guess would be that there would be a difference of a couple of days
when executing something like that on a separate connection to the
server vs on linked tables.

Then, what about upsizing your db to other platforms ...?

--
Roy-Vidar


To be honest I never tried that with ADO.
But I did try that with DAO and ODBC-connections to SQL-server.
Why would ADO make a difference here?
The date-portion of a date-field date is just an integer value isn't it??
Is this not so when using ADO ???

Arno R
Mar 16 '06 #8

P: n/a
Arno

The base date in MS-SQL is January 1, 1900,
The base date in the JET expression service (and VBA), is December 30,
1899.

I haven't tested this but it seems to me (and, I think, Roy) that if
you send MS-SQL the long, zero (0), it will treat this as January 1,
1900, while you may be expecting it to be treated as December 30, 1899.

Of course, this offset will be perpetuated throughout the range of
longs that may be coerced to dates.

IMO, it always good form to use date functions and date variables only
in code, and in SQL to use the string format #yyyy-mm-dd# or
#mm/dd/yyyy# only.

I have tested none of this and there may be some MS magic that makes
what I surmise entirely worng.

Mar 17 '06 #9

P: n/a

"Lyle Fairfield" <ly***********@aim.com> schreef in bericht news:11**********************@j33g2000cwa.googlegr oups.com...
Arno

The base date in MS-SQL is January 1, 1900,
The base date in the JET expression service (and VBA), is December 30,
1899.

I haven't tested this but it seems to me (and, I think, Roy) that if
you send MS-SQL the long, zero (0), it will treat this as January 1,
1900, while you may be expecting it to be treated as December 30, 1899.

Of course, this offset will be perpetuated throughout the range of
longs that may be coerced to dates.

IMO, it always good form to use date functions and date variables only
in code, and in SQL to use the string format #yyyy-mm-dd# or
#mm/dd/yyyy# only.

I have tested none of this and there may be some MS magic that makes
what I surmise entirely worng.


Hmmm, never paid any attention to this difference in base date between SQl and Jet. :-(
As I said: I have a few apps with a SQL-backend (upsized from a mdb backend during the lifetime of the app)
I use ODBC-connections to SQL-server and never noticed a problem using Clng(Date).
Also I have an app like that where I am using Jet-temptables (with lots of date-math) in a separate Temp.mdb.
So I have ODBC-links to SQL and links to the temptables. No problem until now.
Al least no problems are reported with this app (a 7x24 multi-user app with lots of scheduling)
This Clng-'trick' I learned years ago (Access 2.0 days) and I am (or was!) very happy with using the 'trick'.

However I understand what you (and Roy) are saying ...
There are no Australian Dates (as posted in this thread) but SQL-Dates and Jet-Dates are different animals...

I will do some tests to see if I need to be worried.
My guess is the following:
When I use query's, even query's with both jet-tables and SQL-tables
either way Jet does the job, or SQL-server does the job, and I guess both will treat the Clng(Date)-values 'right'.
Or ... am I saying something very stupid here ??

Arno R
Mar 17 '06 #10

P: n/a
Arno R wrote in message <44**********************@text.nova.planet.nl>
:
"RoyVidar" <ro*************@yahoo.no> schreef in bericht
news:mn***********************@yahoo.no...
Arno R wrote in message <44***********************@text.nova.planet.nl>

So StartDate would become Clng(StartDate) and so on.


Arno, have you tried that number trick on SQL server using ADO?

My guess would be that there would be a difference of a couple of days
when executing something like that on a separate connection to the
server vs on linked tables.

Then, what about upsizing your db to other platforms ...?

--
Roy-Vidar


To be honest I never tried that with ADO.
But I did try that with DAO and ODBC-connections to SQL-server.
Why would ADO make a difference here?
The date-portion of a date-field date is just an integer value isn't it??
Is this not so when using ADO ???

Arno R


I haven't bothered testing much either, but threw this little thinge
together just now.

On my setup, it demonstrates that under some circumstances, there will
be an offset of two days when using "semi magic numbers" for date
manipulations.

Create a small table

create table newDateTest (
id int identity primary key,
myDate datetime,
myText varchar(20))

Then try something like this

Sub testing()
Dim cnL As ADODB.Connection
Dim cnD As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db As DAO.Database
Dim strSQL As String

Set cnL = CurrentProject.Connection
Set db = DBEngine(0)(0)
Set cnD = New ADODB.Connection
cnD.ConnectionString = _
"Provider=sqloledb;" & _
"Data Source=steknobb02\stekno;" & _
"Initial Catalog=msp;" & _
"Integrated Security=SSPI"
cnD.Open

strSQL = "insert into dbo.newDateTest " & _
"(myText, MyDate) Values ('ADO OLE DB number'," & _
CLng(Date) & ")"
cnD.Execute strSQL, , adCmdText + adExecuteNoRecords

strSQL = "insert into dbo_newDateTest " & _
"(myText, MyDate) Values ('ADO ODBC number'," & _
CLng(Date) & ")"
cnL.Execute strSQL, , adCmdText + adExecuteNoRecords

strSQL = "insert into dbo_newDateTest " & _
"(myText, MyDate) Values ('DAO ODBC number'," & _
CLng(Date) & ")"
db.Execute strSQL, dbFailOnError

strSQL = "insert into dbo.newDateTest " & _
"(myText, MyDate) Values ('ADO OLE DB date', '" & _
Format$(Date, "yyyy-mm-dd") & "')"
cnD.Execute strSQL, , adCmdText + adExecuteNoRecords

strSQL = "insert into dbo_newDateTest " & _
"(myText, MyDate) Values ('ADO ODBC date', #" & _
Format$(Date, "yyyy-mm-dd") & "#)"
cnL.Execute strSQL, , adCmdText + adExecuteNoRecords

strSQL = "insert into dbo_newDateTest " & _
"(myText, MyDate) Values ('DAO ODBC date', #" & _
Format$(Date, "yyyy-mm-dd") & "#)"
db.Execute strSQL, dbFailOnError

Debug.Print "Get all"
Set rs = cnL.Execute("select * from dbo_newdatetest", , _
adCmdText)
Debug.Print rs.GetString

Debug.Print "Get records for current date"
Debug.Print "through formatting date to string"
strSQL = "select * from dbo_newDateTest where MyDate = #" & _
Format(Date, "yyyy-mm-dd") & "#"
Set rs = cnL.Execute(strSQL, , adCmdText)
Debug.Print rs.GetString

Debug.Print "Get records for current date"
Debug.Print "through converting date to long"
strSQL = "select * from dbo_newDateTest where MyDate = " & _
CLng(Date)
Set rs = cnL.Execute(strSQL, , adCmdText)
Debug.Print rs.GetString

Set db = Nothing
End Sub

On my setup, this gave the following results
Get all
1 19.03.2006 ADO OLE DB number
2 17.03.2006 ADO ODBC number
3 17.03.2006 DAO ODBC number
4 17.03.2006 ADO OLE DB date
5 17.03.2006 ADO ODBC date
6 17.03.2006 DAO ODBC date

Get records for current date
through formatting date to string
2 17.03.2006 ADO ODBC number
3 17.03.2006 DAO ODBC number
4 17.03.2006 ADO OLE DB date
5 17.03.2006 ADO ODBC date
6 17.03.2006 DAO ODBC date

Get records for current date
through converting date to long
1 19.03.2006 ADO OLE DB number

I would primarly recommend utilizing the parameters collection of the
objects one are using, which will not only easily overcome these date
challenges, but also overcome the challenges with single quotes/special
characters in strings and the hassle of working with numbers with
decimals when your regional settings specify comma as decimal
separator.

Oh - not to forget, using parameters also prevents SQL injection
attacks.

When using dynamic SQL - I would support Lyles suggestion of formatting
to string, and use an unambiguous format. yyyy-mm-dd (ISO 8601) or
"escaped US version" mm\/dd\/yyyy.

--
Roy-Vidar
Mar 17 '06 #11

P: n/a

Lyle Fairfield wrote:
IMO, it always good form to use date functions and date variables only
in code, and in SQL to use the string format #yyyy-mm-dd# or
#mm/dd/yyyy# only.


Perhaps in T-SQL strings it's a better or at least equaly good idea to
use:

WHERE t.Date>=cast('2004-02-01' as datetime)

Mar 17 '06 #12

P: n/a
Thanks very much Roy for this very clear explanation!

Arno R

"RoyVidar" <ro*************@yahoo.no> schreef in bericht news:mn***********************@yahoo.no...
Arno R wrote in message <44**********************@text.nova.planet.nl>
:
"RoyVidar" <ro*************@yahoo.no> schreef in bericht
news:mn***********************@yahoo.no...
Arno R wrote in message <44***********************@text.nova.planet.nl>


So StartDate would become Clng(StartDate) and so on.

Arno, have you tried that number trick on SQL server using ADO?

My guess would be that there would be a difference of a couple of days
when executing something like that on a separate connection to the
server vs on linked tables.

Then, what about upsizing your db to other platforms ...?

--
Roy-Vidar


To be honest I never tried that with ADO.
But I did try that with DAO and ODBC-connections to SQL-server.
Why would ADO make a difference here?
The date-portion of a date-field date is just an integer value isn't it??
Is this not so when using ADO ???

Arno R


I haven't bothered testing much either, but threw this little thinge
together just now.

On my setup, it demonstrates that under some circumstances, there will
be an offset of two days when using "semi magic numbers" for date
manipulations.

Create a small table

create table newDateTest (
id int identity primary key,
myDate datetime,
myText varchar(20))

Then try something like this

Sub testing()
Dim cnL As ADODB.Connection
Dim cnD As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db As DAO.Database
Dim strSQL As String

Set cnL = CurrentProject.Connection
Set db = DBEngine(0)(0)
Set cnD = New ADODB.Connection
cnD.ConnectionString = _
"Provider=sqloledb;" & _
"Data Source=steknobb02\stekno;" & _
"Initial Catalog=msp;" & _
"Integrated Security=SSPI"
cnD.Open

strSQL = "insert into dbo.newDateTest " & _
"(myText, MyDate) Values ('ADO OLE DB number'," & _
CLng(Date) & ")"
cnD.Execute strSQL, , adCmdText + adExecuteNoRecords

strSQL = "insert into dbo_newDateTest " & _
"(myText, MyDate) Values ('ADO ODBC number'," & _
CLng(Date) & ")"
cnL.Execute strSQL, , adCmdText + adExecuteNoRecords

strSQL = "insert into dbo_newDateTest " & _
"(myText, MyDate) Values ('DAO ODBC number'," & _
CLng(Date) & ")"
db.Execute strSQL, dbFailOnError

strSQL = "insert into dbo.newDateTest " & _
"(myText, MyDate) Values ('ADO OLE DB date', '" & _
Format$(Date, "yyyy-mm-dd") & "')"
cnD.Execute strSQL, , adCmdText + adExecuteNoRecords

strSQL = "insert into dbo_newDateTest " & _
"(myText, MyDate) Values ('ADO ODBC date', #" & _
Format$(Date, "yyyy-mm-dd") & "#)"
cnL.Execute strSQL, , adCmdText + adExecuteNoRecords

strSQL = "insert into dbo_newDateTest " & _
"(myText, MyDate) Values ('DAO ODBC date', #" & _
Format$(Date, "yyyy-mm-dd") & "#)"
db.Execute strSQL, dbFailOnError

Debug.Print "Get all"
Set rs = cnL.Execute("select * from dbo_newdatetest", , _
adCmdText)
Debug.Print rs.GetString

Debug.Print "Get records for current date"
Debug.Print "through formatting date to string"
strSQL = "select * from dbo_newDateTest where MyDate = #" & _
Format(Date, "yyyy-mm-dd") & "#"
Set rs = cnL.Execute(strSQL, , adCmdText)
Debug.Print rs.GetString

Debug.Print "Get records for current date"
Debug.Print "through converting date to long"
strSQL = "select * from dbo_newDateTest where MyDate = " & _
CLng(Date)
Set rs = cnL.Execute(strSQL, , adCmdText)
Debug.Print rs.GetString

Set db = Nothing
End Sub

On my setup, this gave the following results
Get all
1 19.03.2006 ADO OLE DB number
2 17.03.2006 ADO ODBC number
3 17.03.2006 DAO ODBC number
4 17.03.2006 ADO OLE DB date
5 17.03.2006 ADO ODBC date
6 17.03.2006 DAO ODBC date

Get records for current date
through formatting date to string
2 17.03.2006 ADO ODBC number
3 17.03.2006 DAO ODBC number
4 17.03.2006 ADO OLE DB date
5 17.03.2006 ADO ODBC date
6 17.03.2006 DAO ODBC date

Get records for current date
through converting date to long
1 19.03.2006 ADO OLE DB number

I would primarly recommend utilizing the parameters collection of the
objects one are using, which will not only easily overcome these date
challenges, but also overcome the challenges with single quotes/special
characters in strings and the hassle of working with numbers with
decimals when your regional settings specify comma as decimal
separator.

Oh - not to forget, using parameters also prevents SQL injection
attacks.

When using dynamic SQL - I would support Lyles suggestion of formatting
to string, and use an unambiguous format. yyyy-mm-dd (ISO 8601) or
"escaped US version" mm\/dd\/yyyy.

--
Roy-Vidar

Mar 18 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.