Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • Last Modified:

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
0
westermo
Asked:
westermo
  • 6
  • 3
  • 3
  • +1
1 Solution
 
rockiroadsCommented:
try using format command, does that work?

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

0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
rockiroadsCommented:
Well Leigh your scuppered if one dont have a sense of humour then :)
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
stevbeCommented:
SELECT COUNT(*) FROM tbl_main WHERE Int(Date_Time_Completed) = Date_Created
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
westermoAuthor Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
westermoAuthor Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
westermoAuthor Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
No probs. :-)
0
 
rockiroadsCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now