使用OleDb和Access处理dd/mm/yyyy日期格式 [英] Dealing with dd/mm/yyyy date formats using OleDb and Access

问题描述

我在查询/SQL代码中处理日期时遇到问题.我的区域设置的格式为#dd/mm/yyyy#，因此，在插入日期以访问数据库时，它看起来像#dd/mm/yyyy#，但是通过选择查询，我得到的是#mm/dd/yyyy#

I'm having problems dealing with dates in my queries/SQL code. My regional settings are in the format #dd/mm/yyyy#, so when inserting a date to access database it looks like #dd/mm/yyyy# but with select query i get #mm/dd/yyyy#

Query = "Select EmpEndDate From Employees_Details Where EmpCode = '" & EmpCode & "';"
Objcmd = New OleDbCommand(Query, ObjConnection)
ObjSelect = Objcmd.ExecuteReader
ObjSelect.Read()
Dim EndDate As DateTime
If Convert.ToString(ObjSelect.GetValue(0)) = vbNullString Then
EndDate = DateTime.Now
Else
EndDate = ObjSelect.GetValue(0)
End If

推荐答案

我的区域设置的格式为#dd/mm/yyyy#，因此在插入日期以访问数据库时，它看起来像#dd/mm/yyyy#

My regional settings are in the format #dd/mm/yyyy#, so when inserting a date to access database it looks like #dd/mm/yyyy#

感谢以下来自Plutonix的评论，我意识到您不清楚是要引用VB.NET #date_literal#还是Access SQL #date_literal#.我的答案是关于Access SQL中的日期文字:

Thanks to the comment below from Plutonix I realize that your question is unclear as to whether you are referring to a VB.NET #date_literal# or an Access SQL #date_literal#. My answer is in regard to date literals in Access SQL:

重要的是要认识到Access数据库引擎会忽略Windows中的区域设置"，并且始终会将Access SQL语句中模棱两可的#xx/yy/zzzz#日期文字解释为#mm/dd/yyyy#.使用Windows中默认的英语(英国)"设置的计算机上的以下VB.NET代码可以对此进行说明:

It is important to realize that the Access Database Engine ignores the Regional Settings in Windows and always interprets ambiguous #xx/yy/zzzz# date literals in Access SQL statements as #mm/dd/yyyy#. This can be illustrated with the following VB.NET code on a machine using the default "English (United Kingdom)" settings in Windows:

Imports System.Data.OleDb
Imports System.Globalization

Module Module1

    Sub Main()
        Console.WriteLine("Culture name:")
        Console.WriteLine("    {0}", CultureInfo.CurrentCulture.Name)
        Console.WriteLine("Short date pattern:")
        Console.WriteLine("    {0}", CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern)
        Using con As New OleDbConnection
            con.ConnectionString =
                    "Provider=Microsoft.Jet.OLEDB.4.0;" &
                    "Data Source=C:\Users\Public\mdbTest.mdb;"
            con.Open()
            Using cmd As New OleDbCommand
                cmd.Connection = con
                ' try updating to November 2, 2010 as #dd/mm/yyyy#
                cmd.CommandText =
                        "UPDATE Employees_Details " &
                        "SET EmpEndDate=#02/11/2010# " &
                        "WHERE EmpCode=1"
                Console.WriteLine("UPDATE command:")
                Console.WriteLine("    {0}", cmd.CommandText)
                cmd.ExecuteNonQuery()
                ' read value back and display
                cmd.CommandText =
                        "SELECT EmpEndDate " &
                        "FROM Employees_Details " &
                        "WHERE EmpCode=1"
                Dim EndDate As DateTime = Convert.ToDateTime(cmd.ExecuteScalar)
                Console.WriteLine("Retrieved date:")
                Console.WriteLine("    {0}", EndDate.ToLongDateString)
            End Using
            con.Close()
        End Using
    End Sub

End Module

该代码产生

Culture name:
    en-GB
Short date pattern:
    dd/MM/yyyy
UPDATE command:
    UPDATE Employees_Details SET EmpEndDate=#02/11/2010# WHERE EmpCode=1
Retrieved date:
    11 February 2010

表示#02/11/2010#是作为"2010年2月11日"而不是"2010年11月2日"写入Access数据库的.

showing that #02/11/2010# was written to the Access database as "11 February 2010", not "2 November 2010".

一种更好的方法是通过使用

A much better approach is to avoid date literals in Access SQL statements by using parameterized queries. If date literals must be used in Access SQL statements, always opt for the unambiguous #yyyy/mm/dd# format.

这篇关于使用OleDb和Access处理dd/mm/yyyy日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！