Crash Magic Online
  Intersection Magic
  Map Magic
  Knowledge Base
  Contact Us


Knowledge Base Article:CMO199 

Registration keys moved to client area: Select login on the left menu bar.

Search home page  

SQL Date Formats

Article created: May 20 2008, updated: May 20 2008

Background:  As Crash Magic is used in more international agencies, it is important that SQL is location independent to ensure that dates and times always represent the values we are expecting.

Status:  Info - functionality description
Keywords:  SQL Server,Oracle,ADO,Date,Dates,Localization,DateTime
Categories:   *Data* *Article - fact*


ADO, when passing parameters allows for dates to be passed as strings in a number of different formats. Two of the most common formats (mm/dd/yyyy and dd/mm/yyyy) allow certain dates that are ambiguous, that could be either format. ADO seems to pick a default format, and assume the date is that format, unless it is out of scope in which case it switches to the other format. As a result, this article will explain how Crash Magic is passing dates, to prevent unexpected results.


Dates as Parameters

Whenever a date is passed as a parameter, it will be passed in the following format: yyyy-mm-dd hh:mm:ss. This format will not allow for month and day to be switched.

 Dates in Where Clauses

Dates in where clauses are SQL server dependant, and will be formatted differently depending on which server is selected.

SQL Server

Dates in SQL server will be formatted as ODBC Timestamps. These timestamps are in a specific order:

{ ts 'yyyy-mm-dd hh:mm:ss[.fff]'}

{ d 'yyyy-mm-dd'}

{ t 'hh:mm:ss'}


Oracle dates will be formatted using the To_Date function.

To_Date( [Date],  “mm/dd/yyyy hh24:mi:ss”)

October 23, 2019 3:52AM

© 1999-2019 Pd' Programming, Inc - Lafayette, CO USA