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

Posted on 2006-05-11
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
    LVL 9

    Expert Comment

    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

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

    Expert Comment

    what if the field type of the source field? date? timestamp? text?
    LVL 28

    Expert Comment

    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

    to show you my way which works... to compare with yours

    Author Comment

    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

    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

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

    Accepted Solution

    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

    >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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    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…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now