Solved

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

Posted on 2006-11-10
13
784 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:westermo
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 125 total points
ID: 17915199
try using format command, does that work?

SELECT COUNT(*) FROM tbl_main WHERE Format(Date_Time_Completed,"YYYYMMDD") = Format(Date_Created,"YYYYMMDD")

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17915905
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)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17916368
Well Leigh your scuppered if one dont have a sense of humour then :)
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17916688
SELECT COUNT(*) FROM tbl_main WHERE Int(Date_Time_Completed) = Date_Created
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17916735
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 ;-)
0
 
LVL 2

Author Comment

by:westermo
ID: 17928769
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17928777
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.
0
 
LVL 2

Author Comment

by:westermo
ID: 17928856
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.....?

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17929052
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!
0
 
LVL 2

Author Comment

by:westermo
ID: 17929138
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17929295
No probs. :-)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17929543
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.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17929611
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now