Date format in .accdb

Question

0.00/5 (No votes)

See more:

Good morning everyone.
I am trying to update an .accdb (Access 2007)
One of the rows in table is a date :
Date Type = Date/Time Format = ShortDate

This is being updated from a VB2010.Net application using a SQL Update statement

VB

Ssql = "UPDATE Purchase_Order SET [Po_Rev_No] =  " & lbl_Revision_Number.Text & _
                                           ", [Po_Owner] = '" & GlobalVariables.UserInit & "'" & _
                                           ", [Po_Type] = '" & cbo_Type.Text & "'" & _
                                         ", [Po_Proj_No] = '" & ProjNo & "'" & _
                                        ", [Po_Supplier] = '" & cbo_Supplier.Text & "'" & _
                                         ", [Po_Contact] = '" & cbo_Contact.Text & "'" & _
                                        ", [Po_Currency] = '" & theCurrency & "'" & _
                                ", [Po_Delivery_Address] = '" & GlobalVariables.DeliveryAddr & "'" & _
                                   ", [Po_Delivery_From] = '" & cbo_Delivery_From.Text & "'" & _
                              ", [Po_Delivery_From_Date] = #" & dtp_Delivery_Date_From.Text & "#" & _
                                     ", [Po_Delivery_To] = '" & cbo_Delivery_To.Text & "'" & _
                                ", [Po_Delivery_To_Date] = #" & dtp_Delivery_Date_To.Text & "#" & _
                                     ", [Po_Settle_Disc] =  " & mxt_Settlement_Discount.Text & _
                                       ", [Po_Pay_Terms] = '" & cbo_Payment_Terms.Text & "'" & _
                                           ", [Po_Notes] = '" & rxt_Notes.Text & "'" & _
                                       ", [Po_Incoterms] = '" & cbo_Incoterm.Text & "'" & _
                                ", [Po_Inco_Named_Place] = '" & cbo_Inco_Named_Place.Text & "'" & _
                                      ", [Po_Inco_Place] = '" & cbo_Inco_Place.Text & "'" & _
                                  ", [Po_Submitted_Date] = #" & Today & "#" & _
                                       ", [Po_Auth_Date] = Null" & _
                                         ", [Po_Auth_By] = '" & AuthBy & "'" & _
                                          ", [Po_No_Vat] =  " & chk_No_Vat.Checked & " " & _
                                     " WHERE [Po_Number] = '" & lbl_Order_Number.Text & "'"

           da = New OleDb.OleDbDataAdapter(Ssql, cn)
           Try
               da.UpdateCommand = New OleDbCommand(Ssql, cn)
               da.UpdateCommand.ExecuteNonQuery()
           Catch ex As Exception
               MsgBox("Notify Darrell Immediately - PO Master not updated")
               MsgBox(ex.ToString)
               Exit Sub
           End Try
           ds.Clear()
           da.Dispose()

The table item '[Po_Delivery_From_Date]' contains the value from a DateTimePicker.
If the date = 19/01/2013 (IE 19 January 2013) the database is updated with 19/01/2013.
If the date = 04/02/2013 (IE 4 February 2013) the database is updated with 02/04/2013 (Ie 2 April 2013) <--- THIS IS THE PROBLEM

In my Region and Language settings the short date = dd/mm/yyyy.

Can anyone please tell me why this is happening

Thanks in advance

Darrell

Posted 5-Feb-13 0:24am

Darrell de Wet

Updated 5-Feb-13 1:19am

CHill60

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2013-02-05T01:32:00

Solution 1

Always be explicit with dates when writing to databases to avoid issues like this. Unfortunately most of the tools we use these days seem to assume all programmers live in the US ;-p

To get around the problem replace the line

dtp_Delivery_Date_From.Text

with

dtp_Delivery_Date_From.Value.ToString("dd-MMM-yyyy")

Posted 5-Feb-13 1:32am

CHill60

Comments

Darrell de Wet 5-Feb-13 8:08am

Hi Chill
Thanks for the quick response. I tried your suggestion and got the same result so I swapped the dd and MM around ie("MM/dd/yyyy") and the database now contains the date as i need it.

This makes no sense to me as my (Win 7) Regiona and Language settings are dd/mm/yyyy for short date.

I also tried to run my original SQL statement in Ms Access and had the same original problem. When i tried with your chganges (and swapped the dd & mm) around it achieved the desired result.

the database contains the date as i need it but it thinks it in in format MM/DD/YYYY and i am concerned that it is going to come back and bite me sometime.

Any thoughts?

CHill60 5-Feb-13 8:37am

Did you spot that I had 3 'M's in the format? I.e. It should look like "4 FEB 2013" - this technique usually works. I did a bit of digging around and some other forums have suggested setting the format on the column in Access - I'm not convinced though as internally the dates are stored as numbers, but it might be worth a try

Darrell de Wet 5-Feb-13 8:44am

Ahh good point about the 3 M's - i did not notice that. I'll give it a try and let you know. I can just convert from MMM to MM when i read it back. This problem is driving me nuts.
Thanks for your support.

CHill60 5-Feb-13 8:46am

No problem. You should find when you read it back that it's in short format so no conversion *should* be required - I say that but you never know with Access!! :-)

Darrell de Wet 5-Feb-13 8:49am

You are a star !!! Works !!!
Exactly as you said - no change required when I read it back.
Thank you sooo much