.accdb中的日期格式 [英] Date format in .accdb

问题描述

大家早上好。

我正在尝试更新.accdb（Access 2007）

表中的一行是日期：

日期类型=日期/时间格式= ShortDate



这是使用SQL Update语句从VB2010.Net应用程序更新

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

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

表项''[Po_Delivery_From_Date]'包含DateTimePicker中的值。

如果日期= 2013年1月19日（IE 2013年1月19日），数据库将更新为2013年1月19日。

如果日期= 04/02/2013（IE 4 2013年2月）数据库更新时间为2013年4月2日（即2013年4月2日）< ---这是问题



在我的区域和语言设置中，短日期= dd / mm / yyyy。



任何人都可以告诉我为什么会发生这种情况



提前致谢



Darrell

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

推荐答案

始终写入数据库时​​要明确日期以避免这样的问题。不幸的是，我们现在使用的大多数工具似乎都假设所有程序员都住在美国;-p



要解决问题，请更换

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

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

这篇关于.accdb中的日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！