SQL Server don't want to date me.

[chuckster1]

I'm sure my simple SQL statements worked before but now I'm getting a type convertion error.

The ERROR:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"
Code: 80040e07

I tried Cdate(now())

And

myDate = now()
Isdate(myDate) Returns true but if I put myDate in the strSQL it gives the same error.


STATEMENT 1:

strSQL = "UPDATE storeduseraccess SET item = 'I Do not Accept this Policy', loggeddatetime = '" & now() & "' WHERE NTusername = '" & myUsername & "'"

STATEMENT 2:

strSQL = "UPDATE storeduseraccess SET item = 'I Do not Accept this Policy', loggeddatetime = '" & now() & "' WHERE NTusername = '" & myUsername & "'"

The Error occurs with either statement.

Could the problem be with SQL 2000? The field is a datetime datatype.

Hope you can help.

Chuckster.

 

[Tarwn]

Try hardcoding in a date to see if that makes a differance or still gives you an error. If it makes a differance (ie works) than the problem is with one of your other fields, if it doesn't make a differance I'll try to think about it a little more (ie drink coffee and stare at ceiling in a semblance of wakefulness).

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
This space has nothing in it, it's all ni your imagination

 

[chuckster1]

Hardcoding does work.

I hope you have found some inspiration on your ceiling.

Thanks Tarwn.

Chuckster.

 

[Tarwn]

Ok, got another one Try changing the format of the date like so:

Using Short Date:
strSQL = "UPDATE storeduseraccess SET item = 'I Do not Accept this Policy', loggeddatetime = '" & FormatDateTime(now,2) & "' WHERE NTusername = '" & myUsername & "'"

Using Short Date & Short Time:
strSQL = "UPDATE storeduseraccess SET item = 'I Do not Accept this Policy', loggeddatetime = '" & FormatDateTime(now,2) & " " & FormatDateTime(now,4) & "' WHERE NTusername = '" & myUsername & "'"

The default is a shortdate & long time(or is it vice-versa?) which may be confusing the server.

Let me know how that works, the first shoud be outputting the date exactly like you put it in the string, I'm not sure why it would be messing up with the default date/time format though. Is the db field set to short date maybe?

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
This space has nothing in it, it's all ni your imagination

 

[chuckster1]

How is this: I'm an Aussie and we like our dates dd/mm/yyyy.

My SQL server likes dates in dd/mm/yyyy

Vbscript displays dates in dd/mm/yyyy

If I pass a date where the dd is < 13 then I don't get any errors and SQL records it as an Australian date (which is why the script worked inititally.

If however the dd > 12 then the sql driver (I think) tries to convert it from an American date to Australian and gets and error that the date is out of range. I therefor just convert the Australian date to an American date with this little function. Pass the date from vbscript as an American date and SQL then records it as an Australian date.

Set LoggedDateTime = USdate(now())

Don't we all just love programming? Anyone know if there is a better way to do this?

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function USDate(austDate)

mySplit = split(austDate, &quot;/&quot

USDate = mySplit(1) & &quot;/&quot; & mySplit(0) & &quot;/&quot; & mySplit(2)

End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Thankyou very much for your help Tarwn

Chuckster