[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Convert timestamp in SQL Query to 24-hour rather than 12-hour format

Posted on 2006-05-11
Medium Priority
Last Modified: 2012-05-05
I have a calculation that takes this information from another field: 5/11/2006 10:27 AM
and converts it to: {ts '2006-5-11 10:27:0'}

Here's the calculation:

"{ts '" & Year (fDate_Modified_GREATEST) & "-" & 
Month (fDate_Modified_GREATEST) & "-" & 
Day ( (fDate_Modified_GREATEST)) & " " &
Hour (fDate_Modified_GREATEST)  & ":" & 
Minute (fDate_Modified_GREATEST)  & ":" & 
Seconds (fDate_Modified_GREATEST)   & "'}"

In the above example, it returned records with the following timestamps (data represented exactly how it is on SQL server, format and all)...

5/11/06 9:58 AM
5/11/06 1:35 PM
5/11/06 10:36 AM
5/11/06 12:55 PM
5/11/06 8:43 AM
5/11/06 9:37 AM
5/11/06 12:50 PM
5/11/06 10:39 AM
5/11/06 8:30 AM
5/11/06 2:01 PM
5/11/06 10:27 AM

What's going on here?
Question by:wesbrowning

Expert Comment

ID: 16662051
When you created the calculation field , did you set the type to Text? If you were to output the string in Filemaker what would it contain? Of all the delimiters you used in your string I have only had problems with the "-".

Let me know what you find...


Author Comment

ID: 16662058
Yes, it's set up to be a Text output.
LVL 28

Expert Comment

ID: 16666162
what if the field type of the source field? date? timestamp? text?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 28

Expert Comment

ID: 16666218
just tested your formula and it is correct.
you obviously have a basic error like the output field is not text, because even yr text string "{ts '" is missing.
and concatenating text strings has never been a problem, so that should come up at least...
LVL 28

Expert Comment

ID: 16666235
to show you my way which works... to compare with yours

Author Comment

ID: 16667665
The calculation is inserted into an SQL query, so it ends up looking like this in the SQL Query...

SELECT * FROM "Members" WHERE "Members"."Date_modified" > {ts '2006-5-11 10:27:0'}

I'm thinking that the SQL query and my Filemaker calculations are correct. I just wonder if there's something wrong with the SQL data and the way it's formatted. I've been told that to do an SQL query based on a timestamp, that {ts '2006-5-11 10:27:0'} is the way it needs to be formatted. However, on the other end (in the SQL database), it's formatted like this: 5/11/06 10:27 AM.
LVL 28

Expert Comment

ID: 16668004
sorry, I missed the fact you wanted to get this back from a SQL server, I thought you were just saying it looks like, etc...
I have no SQL server here, and this may vary according to SQL server type (MS?).
It is probably related to the date/time conversion setting of your odbc driver if it has one. If you query a time field, how does it look like?

Author Comment

ID: 16668537
it comes back like this: 5/11/06 10:27 AM
LVL 28

Accepted Solution

lesouef earned 1000 total points
ID: 16668710
don't you have any setting to change that in the odbc parameters?
otherwise, all you've got to do is add to all records finishing with 'PM' an extra 12 hours
LVL 19

Assisted Solution

billmercer earned 1000 total points
ID: 16695790
>I've been told that to do an SQL query based on a timestamp, that {ts '2006-5-11 10:27:0'} is the way it
>needs to be formatted.
True. This Timestamp format...
  {ts '2006-5-11 10:27:0'}
is what's called an escape sequence. It's got nothing to do with the format of the DATETIME value returned by the SQL server in the query results, however. The purpose of this string is to allow software to use a single, standardized format for timestamps regardless of what format the individual databases are using.

>However, on the other end (in the SQL database), it's formatted like this: 5/11/06 10:27 AM.
Actually, this formatting isn't in the SQL database, it's happening when the data is displayed.
In the database itself, the date and time information is stored as numeric data, and converted into a format that makes sense to people only when it's displayed.

If you want to change how the date and time are formatted when returned, you can use the CONVERT() function in your query. This will change the Datetime field into a text string, and apply one of a number of predefined formats to it. For example, instead of doing
  SELECT "SomeDate" FROM "MyTable"

you might do this:
   SELECT CONVERT(varchar, "SomeDate", 120) as "MyFormattedDate"

The number 120 is a "format ID" that tells SQL Server how you want the date formatted. Here's more information on this somewhat confusing function.



Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question