SQL & time

Factual Questions
1

When creating a table with SQL I use data type TIME
this created a field size of 8.

I need the field to take a time in the format
11:05:21 PM

Is there a variation of the TIME data type to make this work?

thanks in advance…

2

Don’t confuse the data itself with the format it gets displayed in. The data is stored the same in any case. What you need to do is change the format of the display.

What system are you using? Most of my SQL experience is in MS Access. Your system may differ.

3

I am not sure where your input into the table will be coming from (a file, online data entry, etc) but a common way to do it is just pass whatever input through this function: TO_DATE(whateverinput,‘HH:MI:SS AM’).

Likewise, when you run queries on the table, just run the output through the same function. The underlying data structure of the table will always be stored the way the database wants to store it. You must format the data as it comes in and out of the field to the format you want to use. BTW, what type of database is this (Oracle, SQL server, or other)?

4

Thanks for the replies

The software I’m using is Pervasive SQL.

What I’m trying to do is duplicate a table that exists in MS Access with Pervasive.

One of the fields in the access table is a Long Date (example data shown in my first post)

Once the table has been created in Pervasive, I link that table into access and then copy/paste the data from the original access table to the new linked one.

Unfortunately though, when using the TIME data type in pervasive, the linked table properties in access show it as text with a field size of 8. So I can’t paste the data because there’s too much information to fit in the field.

Hope that’s clear enough :slight_smile:

Thanks…