SQLTimestamp ( )

Function stats

Average user rating
375
347
9999
Support
FileMaker 7.0 +
Date posted
08 February 2013
Version
1
Recursive function
No

Author Info
 Daniel Smith

8 functions

Average Rating 5.0

author_avatar



 

Function overview

Prototype

SQLTimestamp  ( theTimestamp )


Parameters

theTimestamp  


Description

Convert a timestamp for use in an SQL Query.

Examples

Sample input

Timestamp ( Date ( 1 ; 22 ; 2013 ) ; Time ( 18 ; 23 ; 05 ) )


Sample output

"TIMESTAMP '2013-01-22 18:23:05'

 

Function code

/**
* ============================================================================
* SQLTimestamp ( theTimestamp )
*
* PURPOSE:
*        Convert a timestamp for use in an SQL Query.
*
* RETURNS:
*        (text) timestamp as text
*
* PARAMETERS:
*        theTimestamp (timestamp)
*
* EXAMPLE:
*        SQLTimestamp ( Timestamp ( Date ( 1 ; 22 ; 2013 ) ; Time ( 18 ; 23 ; 05 ) ) ) = "TIMESTAMP '2013-01-22 18:23:05'"
*
* HISTORY:
*        2013-JAN-11 - Created by Daniel Smith dansmith65@gmail.com
* ============================================================================
*/

"TIMESTAMP "
& "'"
& Year ( theTimestamp )
& "-"
& Right ( 0 & Month ( theTimestamp ) ; 2 )
& "-"
& Right ( 0 & Day ( theTimestamp ) ; 2 )
& " "
& Right ( 0 & Hour ( theTimestamp ) ; 2 )
& ":"
& Right ( 0 & Minute ( theTimestamp ) ; 2 )
& ":"
& Right ( 0 & Seconds ( theTimestamp ) ; 2 )
& "'"


/*
// Unit test
Let ( [
    // specify output format
    ~outputEvaluate =
        "$output & If ( $expected = $actual ; \"pass \" ; \"-FAIL- \" )"
        & " & $test & \" | \" & Quote ( $expected ) & \" | \" & Quote ( $actual ) & \¶" ;
    // declare values that are used by multiple tests
        // none to declare for these tests
    
    
    $test = "valid output format" ;
    $value = Timestamp ( Date ( 1 ; 22 ; 2013 ) ; Time ( 18 ; 23 ; 05 ) ) ;
    $expected = "TIMESTAMP '2013-01-22 18:23:05'" ;
    $actual = SQLTimestamp ( $value ) ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    
    // set output to local let variable
    ~output = $output ;
    // Clean up variables
    $test = "" ;
    $value = "" ;
    $expected = "" ;
    $actual = "" ;
    $output = ""
] ;
    ~output
)
*/

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/347

    Prototype: SQLTimestamp( theTimestamp )
    Function Author: Daniel Smith (http://www.fmfunctions.com/mid/375)
    Last updated: 08 February 2013
    Version: 1

*/
// ===================================

 

 

 

 

 

 

 

Top Tags

Text Parsing  (33)
List  (32)
Date  (28)
XML  (26)
Format  (23)
Sql  (22)
Dev  (20)
Debug  (17)
Layout  (15)
Interface  (15)
Text  (14)
Filter  (12)
Variables  (12)
Layout Objects  (11)
Design  (10)
Array  (8)