Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

conversion of a char data type to a datetime

Grrrr ...

I thought I had this working.

I do this

Dim cn
set cn = createobject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Persist Security Info=False;User ID=clientID;Data Source=tcp:x.x.x.x,x;Database=mydb;Integrated Security=SSPI;Language=British"
cn.Execute "INSERT INTO testtable (testdate, testbool) VALUES('" & FormatDateTime(now(), 0) &  "', '2')"
cn.Close
set cn = nothing

I get a crash on
cn.execute etc...


Now I do this

MsgBox CStr(FormatDateTime(now(), 0))

And it says

2006/06/14 11:11:11

But I get the error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Any ideas?
Thanks


0
amoran
Asked:
amoran
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
cn.Execute "INSERT INTO testtable (testdate, testbool) VALUES(convert(datetime,'" & Format(now(), "YYYY-MM-DD HH:NN:SS") &  "'120), '2')"
0
 
amoranAuthor Commented:
Sorry angel its vbscript
0
 
amoranAuthor Commented:
Even if i hardcode it

cn.Execute "INSERT INTO testtable (testdate, testbool) VALUES('1111/11/11 11:11:11','2')"

I get the same error
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ryan ChongCommented:
try:

cn.Execute "INSERT INTO testtable (testdate, testbool) VALUES( GetDate(), '2') "

?
0
 
amoranAuthor Commented:
ryancys great that worked.

The problem now is my .vbs file with the code below works (it inserts into the table)
but when I put it as a Job in SQL Server it does nothing?

Dim cn
set cn = createobject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Persist Security Info=False;User ID=clientID;Data Source=tcp:x.x.x.x,x;Database=mydb;Integrated Security=SSPI;Language=English"
websiteStatus = CStr(WebFileExists("http://dev2.myserver.local"))
cn.Execute "INSERT INTO testtable (testdate, testbool) VALUES(GetDate(),'" & websiteStatus & "')"
cn.Close
set cn = nothing

Function WebFileExists(ByVal vWebAddress)
 Dim oXMLHTTP, vWebText
 Set oXMLHTTP = CreateObject("msxml2.xmlhttp")
 oXMLHTTP.Open "GET", vWebAddress, False
 oXMLHTTP.send
 WebFileExists = 0
 If oXMLHTTP.readyState = 4 And oXMLHTTP.Status = 200 Then WebFileExists = 1
 Set oXMLHTTP = Nothing
 Set vWebText = Nothing
End Function

0
 
satdreamCommented:
can you please check Data Type of "testdate" field. if it's "smalldatetime" change it to "datetime".
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The problem now is my .vbs file with the code below works (it inserts into the table)
but when I put it as a Job in SQL Server it does nothing?

this is usually one of the following problems:
* the sql server agent is not started at all (does the job run/completion message)?
* you specified 2 connection methods:
- >User ID=clientID;Data Source=tcp:x.x.x.x,x;Database=mydb;
-> Integrated Security=SSPI;
  in the first part, you specify a login, but no password.
    => try to add the password and remove second part
  in the second part, you specify to use windows login
    => remove the first part, and ensure the sql server agent service runs under a NT account that has been granted the permissions needed to connect



0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now