Products
  
  
  Crash Magic Online
  
  Intersection Magic
  
  Map Magic
  
  Knowledge Base
  
  
  
  Contact Us
  
  Meeting
  
  Shop
  
  Login
  

 

Knowledge Base Article:CMO199 


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

Knowldegebase:
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*

Explanation:

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.



Solution:

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

Oracle dates will be formatted using the To_Date function.

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


July 22, 2018 12:28PM

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