By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,076 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.

Access/Jet SQL: Date/time issues

100+
P: 108
Greetings,

I'm using Flash MX connecting to an MS Access database via JET.

I've stored dates using Flash/Jet into my database as 'dd/mm/yyyy hh:mm:ss' into a DATE/TIME field.

However I can't update because of the WHERE clause as I am not specifying the date correctly. I believe syntax wise I need to use # and specify it in american format mm/dd/yyyy hh:mm:ss.

I have the code below working :-
Expand|Select|Wrap|Line Numbers
  1. mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate > #02/19/2008#"); 
When I add the time it complains and using the "=" clause it complains :-
Expand|Select|Wrap|Line Numbers
  1. mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = #02/19/2008 12:03:32# "); 
Can anyone please assist, ive spent hours messing around with date/time formats, very confused.

Thanks
Rob W
Mar 19 '08 #1
Share this Question
Share on Google+
11 Replies


100+
P: 108
It appears ok with UK dates it doesnt like the time format for some reason.

Expand|Select|Wrap|Line Numbers
  1. mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = #19/02/2008# "); 
That code above runs without errors, when introducing time hh:mm: or hh:mm:ss it all goes wrong :-(
Mar 20 '08 #2

FishVal
Expert 2.5K+
P: 2,653
It appears ok with UK dates it doesnt like the time format for some reason.

Expand|Select|Wrap|Line Numbers
  1. mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = #19/02/2008# "); 
That code above runs without errors, when introducing time hh:mm: or hh:mm:ss it all goes wrong :-(
Hi, Rob.

First of all you should never use date format other than US in SQL. The example you've provided works with UK format only because Access could not interpret it as valid US format date and makes necessary corrections.

The second thing - could you kindly explain what you mean by " ... it complains ..." in post #1.

The third - try to use single quote delimiters around date constants instead of #.

Regards,
Fish
Mar 20 '08 #3

100+
P: 108
Thanks for the reply.

I amended my actionscript code and now the date is stored as "mm/dd/yyyy hh:mm:ss" which populates the DATE/TIME field (QuizDate) in the access database.

TextDate is a string in actionscript which is used to successfully insert the QuizDate into the table as "mm/dd/yyyy hh:mm:ss"

When I want to update the table based on field Username and Date it goes horribly wrong:-

The code below:-
Expand|Select|Wrap|Line Numbers
  1. mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate =  '"+textDate+"' "); 
Which puts SINGLE QUOTES around the Username and TextDate errors with message
Expand|Select|Wrap|Line Numbers
  1. Data type mismatch in criteria expression SQL statement
This code:-
Expand|Select|Wrap|Line Numbers
  1. mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate =  #"+textDate+"# "); 
Which puts the user name in SINGLE QUOTES and the Date as #mm/dd/yyyy hh:mm:ss#

Which errors with :-
Expand|Select|Wrap|Line Numbers
  1. Parameter object is improperly defined. Inconsitent or incomplete information was provided SQL statement.
Mar 20 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Have you tried to run the same in Access query editor?
Mar 20 '08 #5

100+
P: 108
Thanks for the speedy reply.

When I was storing the dates in UK format in the database I could run the update query in Access with UserName and Date parameters without ANY problems with SINGLE QUOTES or #.

Since I changed the textDate within Actionscript and printed it out to american format "mm/dd/yyyy hh:mm:ss" I assumed it would be stored in the database in this way as I simply insert this String as is to a DATE/TIME field.

I've just checked teh database and its storing it in UK format ???
Suprised me, so the SQL qill be attempting to compare the UK format (stored in database) against textDate field (USA format).

Im looking now at why access would store it in UK format?
Mar 20 '08 #6

FishVal
Expert 2.5K+
P: 2,653
To the best of my knowledge date format is only the way to represent date in Access table/query view unless the table field is of Text type. On the other hand SQL ANSI standard determines date constants to be used in US format.
These are separate things, they are not supposed to interfere.
Mar 20 '08 #7

100+
P: 108
Heres where I am right now with the problem and hopefully a clearer explanation of the current problem:-

I've written an application which stores the users name and current date.

In flash I've formatted a STRING with the date format of "mm/dd/yyyy hh:mm:ss" i.e. American Format

This STRING is then stored into the database without problems in a DATE/TIME field as "dd/mm/yyyy hh:mm:ss' as I am a UK user.

When I wish to update the table based on the value of User name and date the problems occur.

Expand|Select|Wrap|Line Numbers
  1. mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND format(QuizDate,(\"mm/dd/yyyy hh:nn:ss\")) =  #"+textDate+"# ");
Errors with

SQL query has failed for the following reason: Parameter object is improperly defined. Inconsistent or incomplete information was provided SQL statement: UPDATE OverallResults SET Results = 30 WHERE
UserName = 'sf' AND format(QuizDate,(#mm/dd/yyy hh:nn:ss#) = #02/20/2008 15:45:38#

Testing shows if I remove the TIME part hh:mm:ss it runs without errors.

When testing SQL within Access its self all is ok no problems and happy if i use SINGLE QUOTES or HASH for date value, Ive been struggling with this for days.

Any help would greatly be appreciated.

Thanks
Rob W
Mar 20 '08 #8

FishVal
Expert 2.5K+
P: 2,653
Ok, Rob.

I'm somewhat frustrated with it.
Your first syntax was just fine as for Jet SQL.
Expand|Select|Wrap|Line Numbers
  1. mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = #02/19/2008 12:03:32# "); 
I will ask our community to pay attention to your question.

Regards,
Fish.
Mar 20 '08 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Rob

One quick question. When storing the date in the database what is the data type of the field and assuming it's date/time what date format is defined?
Mar 20 '08 #10

Expert 100+
P: 446
Hi Rob

I sympathise because I'm a UK user too. Just a couple of things tho'; in the 'database' the date part is stored as an integer and the time part as decimal day, so the reason for putting a Format() around a date is to inform Access that the character string is to be interpreted as a UK date, so it is then converted to the right valued number for storage. When you view the Table you are seeing it in UK format because of your environment settings.

If you go into Table Design you can change the format to Long Date, Medium date etc. then open the table to see the changes. If you want to be perverse you could change the format to mmm-dd-yyyy then a date of 1st Jan in your table would appear as Jan-01-2008, even though you had entered it as 01-Jan-08.

Now to your problem; when you created the table did you specify General Date (ie with the Time part) ? If you did not I'm pretty sure that Access will not bother with the 'decimal day' and just store the date as an integer. This may be consistent with your findings that it works ok with just dates but 'hh:mm:ss' cause it to fail. So, when you open your table can you see hours-minutes-seconds?

When concatonating in Access(VBA) I always use the ampersand (&) not a plus (+) sign, even though the 'plus' may be acceptable it confuses the hell out of me.

Incidentally, when I want both date and time I (wastefully) create two fields one for date and the other for time. It saves time with the complications your faced with.

S7
Mar 20 '08 #11

NeoPa
Expert Mod 15k+
P: 31,488
Rob,

There seems to be a lot of confusion surrounding this issue so firstly I would suggest reading Literal DateTimes and Their Delimiters (#).

Essentially, you need to understand the difference between what is inside the SQL string and is referring to a literal date (always use # to identify date/times btw) - and what is stored internally as a date.

When you display a date/time field value then it will use your local settings. That certainly does NOT mean it is stored in UK format or whatever else you have set. It is stored as a number (non-integral except for pure dates) and only displayed as a date.

This format is entirely irrelevant when using date literals in SQL. They are in US m/d/yy mode exclusively. Your example earlier worked as Access realised that the particular data you supplied could ONLY be interpreted as d/m/yy so automatically switched it for you. This would not happen for a date such as 2/5/2007 - hence the importance of getting it done the correct way.

@Sierra + =/= & where strings are concerned.
The difference (s1 & s2 == s1 + s2 == s1s2 but s1 & Null == s1 whereas s1 + Null == Null) can be very usefully used in VBA code to drop associated parts of a SQL clause when an item is Null.
Mar 21 '08 #12

Post your reply

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