Time stamp and database

[NicolasChristnacher]

Hi all,

I am facing an issue when I want to insert a Time Stamp in the database.

The format is not which one I attend.

Please could you have a look on the VI ?

[pincpanter]

What's the problem exactly? I don't see anything wrong in your code fragment.

If you are using the DB toolkit, there is a specific vi (DB Tools Format Datetime String (Time Stamp).vi) to properly format the string.

[NicolasChristnacher]

Issue is that my database wait for a time stamp on the format: "2018-06-04 13:06:21"

but with the vi I show you there is a tranformation of the format.

ADO Error: 0x80004005
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.20-log]Incorrect datetime value: '11:27:29.000 06/06/2018' for column 'dateMesure' at row 1 in NI_Database_API.lvlib:Conn Execute.vi->Format Date.vi

[pincpanter]

Can you attach your actual insertion code? Are you using the DB Connectivity Toolkit or not?

[aputman]

Are you inserting a specific timestamp or do you want the current time to be inserted?  If the later, you can set that column to auto-populate with a default value of getdate() function.  This is for MSSQL. 

[Tapan_Mojidra]

You have to set database attribute as a Date or Date and time format and to insert data into table you can use "DB Tools Insert Data.vi", you need to build all table data as a cluster and insert using this vi. 

[NicolasChristnacher · Accepted solution]

Hi all, I fund an other solution, I insert my date/time in a string format with the format I need in my database.

As simple as this:

[aputman]

I would not recommend storing a datestamp into a database as a string.  Use the built in datestamp datatype and store it in the default display format.  There are many in-built functions for operating on datestamps that are made of no effect when you store it as a string.  Not to mention, you won't have a way to query data between certain dates if it is stored as a string.  

Also it doesn't matter the format that is stored in the database.  The format matters when it it being displayed to the user. You will be much better off formatting the date to your liking when the data is queried back to the user.