Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using ISO Notation with SQL Server Dates

Date Handling

Using ISO Notation with SQL Server Dates

by  Norris68  Posted    (Edited  )
SQL Server can handle dates in ISO notation. This avoids any interpretation errors/confusion caused by different international date formats. There are three possibilities: date only, time only or date/time. (Note the curly brackets)
Date Only: {d 'yyyy-mm-dd'}
Time Only: {t 'hh:nn:ss'}
Date/Time: {ts 'yyyy-mm-dd hh:nn:ss'}

The following public enum & function will return a correctly formatted ISO date string:
Code: 
Public Enum SQLDateEnum
    SQLDateOnly
    SQLDateTime
    SQLTimeOnly
End Enum
Public Function SQLDateFormat(InDate As Date, DateFormat As SQLDateEnum) As String
Select Case DateFormat
Case SQLDateOnly:
    SQLDateFormat = "{d '" & Format$(InDate, "yyyy-mm-dd") & "'}"
Case SQLTimeOnly:
    SQLDateFormat = "{t '" & Format$(InDate, "hh:nn:ss") & "'}"
Case SQLDateTime:
    SQLDateFormat = "{ts '" & Format$(InDate, "yyyy-mm-dd hh:nn:ss") & "'}"
End Select
End Function

Usage examples:
Dim SQL As String
SQL = "UPDATE MyTable SET MyText='Hello World' WHERE MyDate = " & SQLDateFormat(Date(),SQLDateOnly)
SQL = "UPDATE MyTable SET MyDate=" & SQLDateFormat(Date(),SQLDateOnly) & " WHERE MyText='Hello World'"
SQL = SELECT * FROM MyTable WHERE MyDate BETWEEN " & SQLDateFormat(DateAdd("d",-3,Date()),SQLDateOnly) & " AND " & SQLDateFormat(Date(),SQLDateOnly)
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top