Datetime with vbscript?

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

Ato
Wed Apr 27 15:27:49 CDT 2005

Have you tried using the FormatDateTime function?

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


<tabladude@gmail.com> wrote in message news:1114622165.252730.57810@l41g2000cwc.googlegroups.com...
> 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
>

kayda
Wed Apr 27 17:53:53 CDT 2005

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/default.asp?url=/library/en-us/script56/html/vsfctformatdatetime.asp

Any ideas why that is happening?

Thanks,
Kayda

Ato
Wed Apr 27 20:18:19 CDT 2005

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@gmail.com> wrote in message news:1114642433.488519.183580@l41g2000cwc.googlegroups.com...
> 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/default.asp?url=/library/en-us/script56/html/vsfctformatdatetime.asp
>
> Any ideas why that is happening?
>
> Thanks,
> Kayda
>