Hi:

There is a datetime field in a SQL Server database. Using ADO, I
extract the maximum date as follows:

strSQL = "SELECT MAX([Timestamp]) AS MaxDate FROM " & TableName

which I then execute. I then set a variable to this value:

varMaxDate = objRS.Fields("MaxDate")

and then I use this to set a SQL Statement:

sSQLStatement = "SELECT * FROM " & TableName & " WHERE Timestamp >= '"
& _
varMaxDate & "'"

The problem is, the resulting SQL Statement is a date, not a datetime.
What is the best way to create a SQL Statement that uses a real
datetime. This SQL Statement is to be used in a SQL Server DTS package
as a query against a Sybase database via ODBC (I'm using VB Script in
an ActiveX Script Task). So my goal is to look in the target table (SQL
Server) for the maximum datetime, and then get any records in the
source (Sybase) with a datetime greater than what is in the SQL Server
database.

Thanks,
Kayda

Have you tried using the FormatDateTime function?

==> varMaxDate = FormatDateTime(objRS.Fields("MaxDate"))


<tabladude (AT) gmail (DOT) com> wrote

I tried doing that but I get a type mismatch error when I run the
package. How is that possible? It is a scripting language so I didn't
declare a datatype when I declared the variable. I tried also supplying
the zero argument as per the description at:

http://msdn.microsoft.com/library/de...atdatetime.asp

Any ideas why that is happening?

Thanks,
Kayda

It's probably a case of the NULLS. Using FormatDateTime on a null value will cause the data mismatch.

So your code should probably look like the following:

tempDate = objRS.Fields("MaxDate"))
If Not IsNull(tempDate) Then
varMaxDate = FormatDateTime(tempDate)
Else
varMaxDate = #1/1/1970#
End If

The #1/1/1970# is there to ensure varMaxDate always contains a legal date (for further processing later on).

"kayda" <tabladude (AT) gmail (DOT) com> wrote