Re: Workdays function with Australian Dates

Thanks very much Roy for this very clear explanation!

Arno R

"RoyVidar" <roy_vidarNOSPAM@xxxxxxxx> schreef in bericht news:mn.8ac07d633bf0e68f.33955@xxxxxxxxxxx
Arno R wrote in message <4419f65a$0$2021$ba620dc5@xxxxxxxxxxxxxxxxxxx>
:
"RoyVidar" <roy_vidarNOSPAM@xxxxxxxx> schreef in bericht
news:mn.84fd7d637ddfdab3.33955@xxxxxxxxxxx
Arno R wrote in message <441967ae$0$23339$ba620dc5@xxxxxxxxxxxxxxxxxxx>


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


.

Relevant Pages

  • Re: Workdays function with Australian Dates
    ... Why would ADO make a difference here? ... cnD.Execute strSQL,, adCmdText + adExecuteNoRecords ... 17.03.2006 ADO ODBC number ...
    (comp.databases.ms-access)
  • Re: Run timer Error
    ... If I work with ADO in mdb, do you know if there is a way equivalent a DAO, I ... Dim DB As DAO.Database, strSQL As String ... primary key avoids duplication records. ...
    (microsoft.public.access.adp.sqlserver)
  • Datumsformat in gespeicherten Views einer Oracle-DB
    ... Meine SQL-Kommandos führe ich nach diesem Muster aus: ... .CommandType = adCmdText ... SQL-Developer einwandfrei funktioniert über ADO Fehlermeldungen verursacht. ... Auch hier erhalte ich die Fehlermeldung ORA-01847. ...
    (microsoft.public.de.vb.datenbank)
  • RE: Excel to Sql
    ... When you are connected to SQL Server in an ADO application by using Microsoft OLE DB for SQL Server (SQLOLEDB), you can use the same "distributed query" syntax from the Using Distributed Queries section to import Excel data into SQL Server. ... Dim strSQL As String ...
    (microsoft.public.data.ado)
  • Re: lengthy operation
    ... I use VC7.1 and ADO. ... pConnection, true, adOpenKeyset, adLockOptimistic, adCmdText); ... Client-sided and UpdateBatch mode as ADO is optimised to populate waves of ...
    (microsoft.public.data.ado)