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: 564
  • Last Modified:

Type mismatch: 'Format'

Hi

I try to do this

cn.Execute "INSERT INTO testtable (testdate) VALUES(" + Format(now(), "yyyy-mm-ddThh:nn:ss") +  ")"

But I get the error


Type mismatch: 'Format'    

Any ideas?

Thanks!
0
amoran
Asked:
amoran
  • 6
  • 4
  • 3
1 Solution
 
TimCotteeCommented:
Hi amoran,

try:

cn.Execute "INSERT INTO testtable (testdate) VALUES('" + Format(now(), "yyyy-mm-dd hh:nn:ss") +  "')"


Tim Cottee
0
 
Éric MoreauSenior .Net ConsultantCommented:
Hi amoran,

You need delimiters (quotes) as Tim showed you but I don't your (first) error comes from there (but it would have cause one later for sure).

try replacing + with &:

cn.Execute "INSERT INTO testtable (testdate) VALUES('" & Format(now(), "yyyy-mm-dd hh:nn:ss") & "')"

Cheers!
0
 
amoranAuthor Commented:
Hi Tim

Still

 Type mismatch: 'Format'  

Thanks

Dates in my DB are like this
2006/04/28 10:00:00
Data Type Datetime
Lenght 8

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Éric MoreauSenior .Net ConsultantCommented:
what about the & ?

the error is raised by the VB compiler?
0
 
amoranAuthor Commented:
The & gave me the same error
0
 
Éric MoreauSenior .Net ConsultantCommented:
are you in vb6 ?

is the error raised by the VB compiler?
0
 
amoranAuthor Commented:
Its in SQL Server Enterprise Manager

Im setting up a Job

I use this code

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,1433;Database=mydb;Integrated Security=SSPI;"
cn.Execute "INSERT INTO testtable (testdate) VALUES('" & Format(now(), "yyyy-mm-dd hh:nn:ss") &  "')"
cn.Close
set cn = nothing
0
 
TimCotteeCommented:
amoran,

Ahhh, so you don't actually have the same functions there. You only have vbscript not full vb functionality. Therefore you need to use
cn.Execute "INSERT INTO testtable (testdate) VALUES('" & FormatDateTime(now(), 0) &  "')"

Tim
0
 
amoranAuthor Commented:
Tim

Tried that but

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

=)

Thanks for the help guys

Increasing points
0
 
amoranAuthor Commented:
Should I be doing something like this

Format(myDateTime, "yyyy-mm-dd hh:nn:ss")

Or is there a VBScript equivalent?
0
 
TimCotteeCommented:
amoran,

Ok, that at least solves the initial problem though.
http://www.prezzatech.com/kb/articles/kb-1005-conversion_char_to_datetime.asp

Will help in guiding you to solve the rest.
Tim
0
 
TimCotteeCommented:
amoran,

The thing is with the formatdatetime function, you don't have the same flexibility as with format, you only have certain fixed formats to choose from. Unless you build your own one, you need to resolve the conflict between the date time formats on your sql server.

Tim
0
 
amoranAuthor Commented:
I tried

cn.Execute "INSERT INTO testtable (testdate) VALUES('" & FormatDateTime(now(), "yyyy/mm/dd hh:nn:ss") &  "')"

but

Type mismatch: '[string: "yyyy/mm/dd hh:nn:ss"]'    
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now