Workdays function with Australian Dates

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

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" <dixie@dogmail.com> wrote in message
news:121grcql52u11d@corp.supernews.com...[color=blue]
>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******
>[/color]

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" <jeff.pritchard@asken.com.au> wrote in message
news:4418847c$0$23317$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> 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" <dixie@dogmail.com> wrote in message
> news:121grcql52u11d@corp.supernews.com...[color=green]
>>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******
>>[/color]
>
>[/color]

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" <dixie@dogmail.com> wrote in message
news:121h2ahfuvnoe9f@corp.supernews.com...[color=blue]
> 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" <jeff.pritchard@asken.com.au> wrote in message
> news:4418847c$0$23317$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
>> 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" <dixie@dogmail.com> wrote in message
>> news:121grcql52u11d@corp.supernews.com...[color=darkred]
>>>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******
>>>[/color]
>>
>>[/color]
>
>[/color]

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

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" <dixie@dogmail.com> schreef in bericht news:121h2ahfuvnoe9f@corp.supernews.com...[color=blue]
> 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" <jeff.pritchard@asken.com.au> wrote in message
> news:4418847c$0$23317$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
>> 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" <dixie@dogmail.com> wrote in message
>> news:121grcql52u11d@corp.supernews.com...[color=darkred]
>>>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******
>>>[/color]
>>
>> [/color]
>
>[/color]

Arno R wrote in message <441967ae$0$23339$ba620dc5@text.nova.planet.nl>
:
[color=blue]
> So StartDate would become Clng(StartDate) and so on.[/color]

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

"RoyVidar" <roy_vidarNOSPAM@yahoo.no> schreef in bericht news:mn.84fd7d637ddfdab3.33955@yahoo.no...[color=blue]
> Arno R wrote in message <441967ae$0$23339$ba620dc5@text.nova.planet.nl>
> :
> [color=green]
>> So StartDate would become Clng(StartDate) and so on.[/color]
>
> 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[/color]

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

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.

"Lyle Fairfield" <lylefairfield@aim.com> schreef in bericht news:1142553750.845510.277470@j33g2000cwa.googlegr oups.com...[color=blue]
> 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.[/color]

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

Arno R wrote in message <4419f65a$0$2021$ba620dc5@text.nova.planet.nl>
:[color=blue]
> "RoyVidar" <roy_vidarNOSPAM@yahoo.no> schreef in bericht
> news:mn.84fd7d637ddfdab3.33955@yahoo.no...[color=green]
>> Arno R wrote in message <441967ae$0$23339$ba620dc5@text.nova.planet.nl>[color=darkred]
>>>[/color]
>>[color=darkred]
>>> So StartDate would become Clng(StartDate) and so on.[/color]
>>
>> 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[/color]
>
> 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[/color]

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

Lyle Fairfield wrote:
[color=blue]
> 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.[/color]

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)

Thanks very much Roy for this very clear explanation!

Arno R

"RoyVidar" <roy_vidarNOSPAM@yahoo.no> schreef in bericht news:mn.8ac07d633bf0e68f.33955@yahoo.no...[color=blue]
> Arno R wrote in message <4419f65a$0$2021$ba620dc5@text.nova.planet.nl>
> :[color=green]
>> "RoyVidar" <roy_vidarNOSPAM@yahoo.no> schreef in bericht
>> news:mn.84fd7d637ddfdab3.33955@yahoo.no...[color=darkred]
>>> Arno R wrote in message <441967ae$0$23339$ba620dc5@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[/color]
>>
>> 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[/color]
>
> 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
>
>[/color]

Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

re: Workdays function with Australian Dates

/bytes/development

/bytes/it

/bytes/about