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

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?
wesbrowningAsked:
Who is Participating?
 
lesouefConnect With a Mentor Commented:
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
0
 
jvaldesCommented:
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...

Jvaldes
0
 
wesbrowningAuthor Commented:
Yes, it's set up to be a Text output.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lesouefCommented:
what if the field type of the source field? date? timestamp? text?
0
 
lesouefCommented:
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...
0
 
lesouefCommented:
http://www.lesouef.net/files/timestamp.fp7
to show you my way which works... to compare with yours
0
 
wesbrowningAuthor Commented:
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.
0
 
lesouefCommented:
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?
0
 
wesbrowningAuthor Commented:
it comes back like this: 5/11/06 10:27 AM
0
 
billmercerConnect With a Mentor Commented:
>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.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp 

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.