Link to home
Start Free TrialLog in
Avatar of westermo
westermo

asked on

VBSCRIP "DateValue in SQL giving "Data Type Mismatch" - why?

Hi, I am using the following to calculate number of records that have two date fields using the same date.  However one of the date fields stores BOTH date and time - the other only Date.  Both fields are set as Date data types.

Problem is that when this statement runs, my data access page throws up "Data Type Mismatch" error.

Why is this?

---------
SELECT COUNT(*) FROM tbl_main WHERE DateValue(Date_Time_Completed) = Date_Created
---------

Many thanks
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or another way for fun

SELECT COUNT(*) FROM tbl_main WHERE Date_Time_Completed >= Date_Created AND Date_Time_Completed < DateAdd(d, 1, Date_Created)
Well Leigh your scuppered if one dont have a sense of humour then :)
Avatar of stevbe
stevbe

SELECT COUNT(*) FROM tbl_main WHERE Int(Date_Time_Completed) = Date_Created
Hmmm - why did I think this had to be in T-SQL?
Weird.

Oh well - Dateadd would just need a couple of quote marks in there.  DateAdd("d",... etc
But what are a couple of quote marks between friends ;-)
Avatar of westermo

ASKER

Aaah thanks - I think I understan why this problem came about - something to do with the way dates are used in SQL/JET setting the same format solves it.

Anyway, I tried the first suggestion from rockiroads, changing the " for ' and it worked great.

So thanks rockiroads and to all others who offered.
Not to deminish Rocki's answer - but do you understand why then?
You mention that you do but then "something to do" isn't necessarily certain ;-)

FWIW if you examine all the answers (Rock's, Steve's and mine) it might help gain an understanding.
If you are cool with it excellent.
If not - let us know - and it can be spelled out.
Thanks LPurvis, I didn't expect to be validated on this - but good on ya!!

I beleive that this issue relates to the the formatting of the 'stored' date in the JET SQL engine e.g. MMDDYYYY whilst something in Access uses a diffrent way? like DDMMYYYY.

So by setting a uiniform format for the engine to work with when cacluating differences, you overcome this.....?

Umm... no.
:-)

Interesting point though.
International differences in date formats do sometimes have to be catered for if you live in a non US format zone.  (As Rocki and I do for example!)
However that isn't what was happening here (and I felt that - although completely correct - the example you're using might not explicitly show the reason).

The fundamental issue you had is that one of your dates had a time component.
All date fields actually have the capacity for a time component - but it's often just 0.

Dates are stored as a double precision type number - with the integer part of the value specifiying the number of days after 30th Dec 1899.
The decimal component (if present) represents the percentage of a day - i.e. the time.
(e.g. .5 is midday obviously).
But 1000.5 is not equal to 1000.

What Rocki's answer did was strip away the time part using formatting.
Formatting your date fields as text - with date only portions, so the text matches on the date alone.
Steve's solution was similar - it uses Int to leave only the integer portion of the date value (i.e. the date).
My suggestion - while the most convoluted, can be very useful - it basically just says any date and time between the start of that day and before the next day.  (And so allows the time component to remain).

Hope this makes sense.

Have fun!
I see it very clearly now.  Looking at all the methods supplied here, you have all helped me understand this problem.

So thanks for the comprehensive overview.  I am a great believer of understanding 'why', not just 'how'.  And I can now appreciate that perhaps the points should have been awarded to you all, rather than the 1st offered solution that I tried....

Thanks once again. :D
No probs. :-)
I tend to have always used the Format command to perform date checks, I guess a hangover from vb days.
I find it most useful as I force both dates Im checking against to be in the same format.

This then addresses what LPurvis mentioned about time and different date formats. This is what I believe anyway and Ive never had a problem using it, thats why I often use Format as my answer to date checks, even the occasional order by.

There may be a slight overhead in performance but has yet, it hasnt affected any of my apps.
I'm fine with Rocki's decisions for the most part - though in my past experience (/sad tests for the sake of it) when you have an index on the date field - you can experience a severe loss in performance on large tables (where the index really makes a difference).
(A function call on the field invalidates the index for use in query engine optimization).  Which is where my less than / greater than comes in.