Solved

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

Posted on 2006-11-10
13
826 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

738 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