Format a date time string

Question

Im having a problem formatting a datetime field in ASP Classic. The format I need is yyyy-mm-dd hh:mm:ss this is my code so far:

changeToDate = cdate(Request.Form("datepicker")&" "&Request.Form("timepicker")) 

dateTime = DatePart("d", changeToDate)&"-"&DatePart("m", changeToDate)&_
           "-"&DatePart("yyyy", changeToDate)&" "&DatePart("hh", changeToDate)&":"&_
           DatePart("mi", changeToDate)&":00"

but this gives me a invalid procedure call datepart exception. I'm kind of stuck on this.

Not sure if it matters or not but I am doing this so that I can insert the datetime into the database. when i just use the cdate function I get the following error:Conversion failed when converting date and/or time from character string. This is what my query looks like with just the cdate function called:

INSERT INTO mytable(Client ,Campaign ,Location ,CardCode ,Created ,Message ,Rating ,Info ,mobile ,DataMessage ) VALUES (117,227,1487,'NJPNUYX','10/07/2013 9:10:00 a.m.','NJPNUYX 4 safda',4,'safda','0215833650','0223232345')

could you please tell us exactly where the exception is thrown and what the error message says?? — Vogel612, Jul 01 '13 at 07:42
I suspect that this is in a build up to passing the date to a database - if this is the case, please stop doing this - pass the date across in a parameter, **as a date** and let ADO/the database system work out how to translate VB dates into the database server's representation. Formatting dates as strings is one of the largest sources of bugs. — Damien_The_Unbeliever, Jul 01 '13 at 08:23
@ damien_the_unbeliever, yes it is to put it into the database. I did try to insert it with just after the cdate function but got an error for it saying it was the wrong format which is why i am trying to convert it to the right format. — user2255811, Jul 01 '13 at 21:03
As I said, in that case, you ought to use [parameters](http://msdn.microsoft.com/en-us/library/windows/desktop/ms675869(v=vs.85).aspx) - in this way, you can pass it across as a *date* rather than as a string. It's you forcing things into a string that is *causing* the formatting issue in the first place. — Damien_The_Unbeliever, Jul 02 '13 at 06:41

Castrohenge · Answer 1 · 2013-07-01T11:09:17.083

I think you're using the incorrect parameters in your hour and minute DatePart calls.

According to MSDN you need to use the following:

hour - h (instead of hh)
minute - n (instead of mi)

Change:

DatePart("hh", changeToDate) & ":" & DatePart("mi", changeToDate)

to

DatePart("h", changeToDate) & ":" & DatePart("n", changeToDate)

For more info see http://msdn.microsoft.com/en-us/library/4kt42529%28v=vs.84%29.aspx

Format a date time string

1 Answers1