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

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