amoran
asked on
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.Connec tion")
cn.Open "Provider=SQLOLEDB;Persist Security Info=False;User ID=clientID;Data Source=tcp:x.x.x.x,x;Datab ase=mydb;I ntegrated Security=SSPI;Language=Bri tish"
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
I thought I had this working.
I do this
Dim cn
set cn = createobject("ADODB.Connec
cn.Open "Provider=SQLOLEDB;Persist
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(),
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
cn.Execute "INSERT INTO testtable (testdate, testbool) VALUES(convert(datetime,'" & Format(now(), "YYYY-MM-DD HH:NN:SS") & "'120), '2')"
ASKER
Sorry angel its vbscript
ASKER
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
cn.Execute "INSERT INTO testtable (testdate, testbool) VALUES('1111/11/11 11:11:11','2')"
I get the same error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Connec tion")
cn.Open "Provider=SQLOLEDB;Persist Security Info=False;User ID=clientID;Data Source=tcp:x.x.x.x,x;Datab ase=mydb;I ntegrated Security=SSPI;Language=Eng lish"
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.xmlht tp")
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
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.Connec
cn.Open "Provider=SQLOLEDB;Persist
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.xmlht
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
can you please check Data Type of "testdate" field. if it's "smalldatetime" change it to "datetime".
>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;Datab ase=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
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;Datab
-> 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