By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Date format in query issue

100+
P: 121
Hi,

I am trying to run a query on a date criteria the date data in the table that I am searching is in this format dd/mm/yyyy hh:mm I need to have the date in this format for various reasons.

The user selects the date from a calendar control which is in this format dd/mm/yyyy which is what I want the query to search on because they are in two different formats the query will not return any results.

I have formatted the fields within the field properties in the query with no success I have also tried creating a sub query with expressions that convert the date value into the format that I need with no success.

Hope this makes sense please help

Regards Phill
May 10 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
Hi phill86. When using dates it is not the formatting which is important, but whether or not the values you are comparing really are of type date/time. The format applied to a date/time value is simply how you choose to display the value - it does not change how it is stored internally in the database.

In MS products such as Access and Excel a date/time value is stored internally as a floating-point number, with the whole-number part representing the date and the decimal part the time. The date part is stored as the relative number of days from 1 Jan 1900.

The calendar control is most likely returning a string value representing the current date. To make comparisons on date values using the built-in date functions you may have to explicitly typecast the value (meaning change it to a specified data type). Access provides a function called CDate to do this.

I don't know what comparison you are trying to do, as you have not posted the SQL for it. Using CDate (on both sides of the comparison as a belt and braces approach), the general form for such a comparison is as follows

Expand|Select|Wrap|Line Numbers
  1. SELECT field1, field2, ..., fieldN
  2. FROM sometable
  3. WHERE CDate(SomeDate) >= CDate(SomeEarlierDate)
  4. ORDER BY something;
Try using CDate and see how you get on. If this does not do the trick, please post the SQL for your query and we'll have a look at it for you.

Regards

-Stewart

ps the following HowTo article by our very experienced Admin contributor NeoPa may help you understand why issues arise with date/time values. It is specifically about date literals, but also provides some useful general information about ANSI SQL expectations of date formats. It is linked here.
May 10 '08 #2

100+
P: 121
Hi phill86. When using dates it is not the formatting which is important, but whether or not the values you are comparing really are of type date/time. The format applied to a date/time value is simply how you choose to display the value - it does not change how it is stored internally in the database.

In MS products such as Access and Excel a date/time value is stored internally as a floating-point number, with the whole-number part representing the date and the decimal part the time. The date part is stored as the relative number of days from 1 Jan 1900.

The calendar control is most likely returning a string value representing the current date. To make comparisons on date values using the built-in date functions you may have to explicitly typecast the value (meaning change it to a specified data type). Access provides a function called CDate to do this.

I don't know what comparison you are trying to do, as you have not posted the SQL for it. Using CDate (on both sides of the comparison as a belt and braces approach), the general form for such a comparison is as follows
Expand|Select|Wrap|Line Numbers
  1. SELECT field1, field2, ..., fieldN
  2. FROM sometable
  3. WHERE CDate(SomeDate) >= CDate(SomeEarlierDate)
  4. ORDER BY something;
Try using CDate and see how you get on. If this does not do the trick, please post the SQL for your query and we'll have a look at it for you.

Regards

-Stewart

ps the following HowTo article by our very experienced Admin contributor NeoPa may help you understand why issues arise with date/time values. It is specifically about date literals, but also provides some useful general information about ANSI SQL expectations of date formats. It is linked here.
Hi Stewart

Both the StartTime and CalendarDate fields are both set to Date/Time. If i input the date in the StartTime field as this format dd/mm/yyyy it works fine but as soon as i input it as dd/mm/yyyy hh:mm it stops working.

Many thanks Phill
Expand|Select|Wrap|Line Numbers
  1. SELECT T_ActiveSession.SessionID, T_ActiveSession.StudioName, T_ActiveSession.StartTime, T_ActiveSession.EndTime, T_Studio.StudioID
  2. FROM T_Studio INNER JOIN T_ActiveSession ON T_Studio.StudioID = T_ActiveSession.StudioID
  3. GROUP BY T_ActiveSession.SessionID, T_ActiveSession.StudioName, T_ActiveSession.StartTime, T_ActiveSession.EndTime, T_Studio.StudioID
  4. HAVING (((T_ActiveSession.StartTime)=[Forms]![F_EquipBooking]![SF_CalDate].[Form]![Calendardate]) AND ((T_Studio.StudioID)=[Forms]![F_EquipBooking]![Combo150]));
May 10 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi Phill. In the HAVING clause you are comparing for equality a date/time value (StartTime) to a date (the calendar control). This in general will not produce any match, as the default time value of midnight (if I remember correctly) for a date without a time will not match most start times you have in your database. To resolve this you need to compare the dates without the times. I suggest using the datevalue function on your starttime to return the date part of the date/time you want to compare, as follows:

Expand|Select|Wrap|Line Numbers
  1. HAVING (((DateValue(T_ActiveSession.StartTime))=[Forms]![F_EquipBooking]![SF_CalDate].[Form]![Calendardate]) AND ((T_Studio.StudioID)=[Forms]![F_EquipBooking]![Combo150]));
You may still have to use CDate on either or both sides of the comparison, but try the datevalue function first.

-Stewart
May 10 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
Stewart's correct! A date entered as dd/mm/yyyy will on ever match a date entered as dd/mm/yyyy hh:mm at midnight on the given date!


Linq ;0)>
May 10 '08 #5

P: 69
Another caution about dd/mm/yy - remember that US custom is to use mm/dd/yy. If you are not viigilant, you can get unexpected answers when doing date arithmetic. For this reason, my practice is to use yyyy/mm/dd and then Cdate for absolute safety. Remember also that in SQL a "#" is needed on either side of a date.

Regarding the need for date and time, I'd suggest entering them in separate fields and adding to get the decimal version if necessary.

If, as I do, you live outside the USA, the mm/dd format is a constant irritation, like the A4/Letter paper issue!
May 11 '08 #6

NeoPa
Expert Mod 15k+
P: 31,487
...
For this reason, my practice is to use yyyy/mm/dd and then Cdate for absolute safety.
...
In that case I suggest you take a look at the same tutorial (Literal DateTimes and Their Delimiters (#)) for a fuller understanding of the issues. With that understanding the problems are always much clearer and easier to deal with.

Personally, I would never suggest using yyyy/mm/dd in SQL, although I often suggest using reverse notation when applying dates to filenames. That way files can be sorted by name and also reflect the dates.
May 12 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.