Access/Jet SQL: Date/time issues

[robtyketto]

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

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

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

[robtyketto]

It appears ok with UK dates it doesnt like the time format for some reason.

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

[FishVal]

It appears ok with UK dates it doesnt like the time format for some reason.

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

[robtyketto]

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

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

Data type mismatch in criteria expression SQL statement

This code:-

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

Parameter object is improperly defined. Inconsitent or incomplete information was provided SQL statement.

[FishVal]

Have you tried to run the same in Access query editor?

[robtyketto]

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?

[FishVal]

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.

[robtyketto]

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.

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

[FishVal]

Ok, Rob.

I'm somewhat frustrated with it.
Your first syntax was just fine as for Jet SQL.

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.

[MMcCarthy]

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?

[sierra7]

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

[NeoPa]

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.