Data format options for Column parameters

Hi Nathan and the Slingshot community. I have been working to create a mySQL database to store temperature and environment information.

I was wondering if you knew what types of data formats could be specified when setting up a new column parameter? At the moment, I am trying to format a valid timestamp via the Datetime data type. Using the web (http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx) to reference possible options I tried a couple of different formats;

Data Code -- String Format

Datetime(121) -- yyyy-mm-dd hh:mi:ss:mmm

Datetime(102) -- yy.mm.dd

Datetime(114) -- hh:mi:ss:mmm

After trying to specify that with the Column parameters, the command returned errors, so I stripped the (Code) part and was able to create the columns. Now I am trying to feed data into the table's columns and have been returned with an error informing me that thedatetime format: yyyy-mm-dd hh:mi:ss:mmm is invalid.

Any thoughts on how I might get this to work using a timestamp/ datetime format? I can use a simple Char(32) data type but it would be great to use an actual format that the SQL database could interpret with a little bit more intelligence when I want to retrieve specific data later.

Replies to This Discussion

Permalink Reply by Nathan Miller on July 17, 2012 at 6:59pm

Hello Tyler,

You might want to give this a read from the MySQL 5.0 Manual. Remember... SQL Server does not equal MySQL. There are differences in data types and syntax.

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

11.3.1. The DATE, DATETIME, and TIMESTAMP Types
The DATE, DATETIME, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes DATE, DATETIME, and TIMESTAMP values in several formats, described in Section 9.1.3, “Date and Time Literals”. For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.

-Nate

RSS