'800a000d' type mismatch

[middletree]

I've been messing with this for hours, and have been to various sites,
including Aaron's site, and am truly stumped.

The short version: in SQL Server, the 4 fields in question are datetime. I
have a page that allows users to type in the info (actually a calendar
pop-up allows me to make sure they do it in the correct format), into a
textbox.

When this page gets called up again, I want to prefill the box with the
existing data, in case they need to change it. This is where the problem
comes in. SQL Server has gone and changed the time to a format which has AM
or PM at the end. And that's fine. But this calendar deal picks up the time
without the PM. So all of a sudden, 1:30 PM becomes 1:30, which then gets
saved as 1:30 AM.

Because the calendar depends a lot on Javascript, I am not willing to change
the code too much, as I have very little JS knowledge. So I have decided to
use ASP to change the data after I have retrieved it.

Here's my code:

dtTempDate = rs("TempSolution")
str24hourDate = FormatDateTime(dtTempDate,2)
str24hourTime = FormatDateTime(dtTempDate,4)
int24hourSecond = DatePart("s",dtTempDate)

if int24hourSecond = 0 then
str24hourSecond = "00"
else
if int24hourSecond < 10 then
str24hourSecond = "0" & cstr(int24hourSecond)
else str24hourSecond = Cstr(int24hourSecond)
end if
end if

strTempSolution = str24hourDate & " " & str24hourTime & ":" &
str24hourSecond


I am getting this error:

=======
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'FormatDateTime'
=======

And the line cited is

str24hourDate = FormatDateTime(dtTempDate,2)

which is the first line where I try to do any formatting.

When I comment out that line, I just get the same error on the next line.

What's wierd is that this was working, and just stopped working. I promise I
didn't change a thing between the time it worked and when it didn't.

Any help resolving this error appreciated.

 

[Steven Burn]

What happens when you CDate() and IsDate() rs("TempSolution") ??

e.g.

Response.Write "CDate: " & CDate(rs("TempSolution")) & "<br>"
Response.Write "IsDate: " & IsDate(rs("TempSolution"))

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[middletree]

Yeah, I guess because I didn't want to have a long post, I didn't mention
everything I had tried, but I should have at least alluded to it. I did all
kinds of things with cstr, cdate, etc. Got the same error each time. Don't
think I tried IsDate, though.

 

[Aaron Bertrand [SQL Server MVP]]

comes in. SQL Server has gone and changed the time to a format which has

AM
or PM at the end.

No, it has not. SQL Server does *NOT* store the datetime value in a humanly
readable format it is stored as two integers. Your query is what is
producing the format with the AM/PM on the end. If you don't want to select
that format, then you can use convert. E.g., hopefully you are using a sane
and unambiguous date format, like YYYYMMDD or YYYY-MM-DD, then you can say:

SELECT
CONVERT(CHAR(8), dateColumn, 112) + ' '
+ CONVERT(CHAR(8), dateColumn, 108)
FROM Table

This will yield YYYYMMDD HH:MM:SS. If you want YYYY-MM-DD, use 120 instead
of 112. I hope we don't have to go over, again, why MM/DD/YY or DD/MM/YY is
a horrible format and should be avoided like the plague.

if int24hourSecond = 0 then
str24hourSecond = "00"
else
if int24hourSecond < 10 then
str24hourSecond = "0" & cstr(int24hourSecond)
else str24hourSecond = Cstr(int24hourSecond)
end if
end if

Ugh. Why are you doing all this? If you have the time in a format like
YYYYMMDD HH:MM:SS, you can just say right(thatVariable,2).

What's wierd is that this was working, and just stopped
working.

If the above doesn't help, then can you change back to the original query,
and tell us what this yields?

on error resume next
response.write dtTempDate
response.write "<br>"
response.write typeName(dtTempDate)
response.write "<br>"
response.write typeName(cdate(dtTempDate))
response.end

 

[Steven Burn]

This will yield YYYYMMDD HH:MM:SS. If you want YYYY-MM-DD, use 120 instead
of 112. I hope we don't have to go over, again, why MM/DD/YY or DD/MM/YY is
a horrible format and should be avoided like the plague.

lol..... it is?

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[middletree]

Aaron, I understand where you're coming from, but for this Intranet app, the
MM/DD/YYYY format works fine and is the preferred format for what we're
doing with the data.

I did change my select to do a CONVERT and change the format, but the
CONVERT/CAST options don't have one that works for my preferred format. The
only 24-hours one, according to BOL, have the date in the wrong order for
what I'm doing.

Having said all of that, I found out that I don't need to do all that stuff
in my previous code. It turns out that the type mismatch was a simple case
of not accounting for the possibility of a NULL value. Once I added that in,
it works fine.

James

 

[Aaron Bertrand [SQL Server MVP]]

CONVERT/CAST options don't have one that works for my preferred format.

The
only 24-hours one, according to BOL, have the date in the wrong order for
what I'm doing.

I'm not sure I understand. The very sample I showed you converted the date
and time separately using different CONVERT statements (because I realize
the all-for-one versions are limited). So you could certainly say:

SELECT CONVERT(CHAR(10), GETDATE(), 103) + ' ' + CONVERT(CHAR(8), GETDATE(),
108)

Which will yield:

dd/mm/yyyy hh:mm:ss

It turns out that the type mismatch was a simple case
of not accounting for the possibility of a NULL value.

And this is why I asked for the information previously, about what value is
actually IN dtTempDate etc.

 

[middletree]

I'm not sure I understand. The very sample I showed you converted the date
and time separately using different CONVERT statements (because I realize
the all-for-one versions are limited). So you could certainly say:

I think I misread that part of your post. Right before it was the part that
yielded YYYY first, and then your comment about why my format is ugly. In
between those two was the solution I needed, and I failed to catch that.
Because of an interruption as I was reading it. No excuse. My apologies.

And this is why I asked for the information previously, about what value is
actually IN dtTempDate etc.

Yeah, it's because you asked for it that I realized it was null.

Pretty frustrating error message, because when I see "Type Mismatch", I am
thinking that I am trying to add 2 Social Security numbers together, or
putting alphabetic characters in an integer field, or some such thing.
Trying to display a date from a NULL value is, technically speaking, a type
mismatch, but it wasn't the first thing that came to mind. Now I know
better.