Access SQL Date Format

[Goh]

Hi,

I got a big problem, inconsistent date format in access. I dun know why
when I do query in access wich parameter dd-mm-yyyy when it goes to database
it became mm-dd-yyyy.

Okie let me explain more detail. I writing a vb6 program that will doing
search to table in MS Access. the Query String in vb6 that i use is simple
like this "select * from table1 where StartAt <=#5/8/06#" when u saw this I
will assume is 5th of ogos 06. but MS access will interpret as 8th Mei 06.
My Computer date format is dd-mm-yyyy.
more over I already define the format for that StartAt column as
dd-mm-yyyy why still cannot follow the rules.

How can we actually define the date format in database.

Please help me is a serious problem to me. The whole system work like crazy
now.

Thank in advance.
With regards,
Goh

 

[Guest]

SQL (an American invention) uses American date formats.

SQL, used internationally, supports several date formats, but
the standard is yyyy mm dd

Windows and Access support and use other date formats
for the Windows and Access user interfaces, which is nice.

When you use the Windows and Access user interfaces,
you should use the date formats you have specified for the
Windows and Access user interfaces.

And when you use SQL you need to use the SQL date formats.

Either Month Day Year, or Year Month Day.

If you use Year Month Day, you need to use four digits for the
year to specify that you are using the standard date format rather
than the original date format.

(david)

 

[Ken Snell \(MVP\)]

The Jet database engine (what runs the SQL statements) expects date values
to be in an unambiguous format (e.g., yyyy-mmm-dd) or else it assumes that
you're using the mm/dd/yyyy format. So I always cast date values with the
Format function to ensure that the date is in the mm/dd/yyyy format.

 

[Pieter Wijnen]

ie
SQLDate(ByVal V As Variant) As String
If IsNull(V) Then
SQLDate = "Null"
Else
SQLDate = "#" & Format(V,"mm\/dd\/yyyy") & "#"
End If
End Function

HTH

Pieter

 

[Pieter Wijnen]

ie
SQLDate(ByVal V As Variant) As String
If IsNull(V) Then
SQLDate = "Null"
Else
SQLDate = "#" & Format(V,"mm\/dd\/yyyy") & "#"
End If
End Function

HTH

Pieter

Hi,

I got a big problem, inconsistent date format in access. I dun know why
when I do query in access wich parameter dd-mm-yyyy when it goes to
database it became mm-dd-yyyy.

Okie let me explain more detail. I writing a vb6 program that will
doing search to table in MS Access. the Query String in vb6 that i use is
simple like this "select * from table1 where StartAt <=#5/8/06#" when u
saw this I will assume is 5th of ogos 06. but MS access will interpret as
8th Mei 06. My Computer date format is dd-mm-yyyy.
more over I already define the format for that StartAt column as
dd-mm-yyyy why still cannot follow the rules.

How can we actually define the date format in database.

Please help me is a serious problem to me. The whole system work like
crazy now.

Thank in advance.
With regards,
Goh

--

 

[Dragan Blagojevic]

I had lot of fun with the same, so few years back i made little func to
alway format date to proper SQL format.
Just use it wherever you need to pass date parameter to query

Regards
Dragan


Function FormatToSQLDate(dDate As Variant, Optional bYearFirst As Boolean =
False) As String
Dim cMonth$
Dim cDay$
Dim cYear$

On Error GoTo Err_Function

If IsNull(dDate) Then

If bYearFirst Then
FormatToSQLDate = "0000/00/00"
Else
FormatToSQLDate = "00/00/0000"
End If
Exit Function
End If
If IsEmpty(dDate) Then
If bYearFirst Then
FormatToSQLDate = "0000/00/00"
Else
FormatToSQLDate = "00/00/0000"
End If
Exit Function
End If
If dDate = "" Then
If bYearFirst Then
FormatToSQLDate = "0000/00/00"
Else
FormatToSQLDate = "00/00/0000"
End If
Exit Function
Else
cMonth = Month(dDate)
If CInt(cMonth) < 10 Then cMonth = "0" & cMonth
cDay = Day(dDate)
If CInt(cDay) < 10 Then cDay = "0" & cDay
cYear = Year(dDate)
If bYearFirst Then
'FormatToSQLDate = Format$(dDate, "yyyy/mm/dd")
FormatToSQLDate = cYear & "/" & cMonth & "/" & cDay
Else
'FormatToSQLDate = Format$(dDate, "mm/dd/yyyy")
FormatToSQLDate = cMonth & "/" & cDay & "/" & cYear
End If
End If


Exit_Function:

Exit Function

Err_Function:
MsgBox Err.Description
cMonth = "00"
cDay = "00"
cYear = "0000"
Resume Exit_Function

End Function