GetAsSqlDate ( )

Function stats

Average user rating
5.0000
375
342
9999
Support
FileMaker 7.0 +
Date posted
06 December 2012
Version
1
Recursive function
No

Author Info
 Daniel Smith

8 functions

Average Rating 5.0

author_avatar



 

Function overview

Prototype

GetAsSqlDate  ( dateFromSqlResult )


Parameters

dateFromSqlResult  date as text, as returned from ExecuteSQL function


Description

Tags:  Sql   Date  

Returns date parsed from text in the format of "YYYY-MM-DD".

Examples

Sample input

"2012-12-05"


Sample output

12/5/2012

 

Function code

/**
* ============================================================================
* GetAsSqlDate ( dateFromSqlResult )
*
* RETURNS:
*        Date parsed from text in the format of "YYYY-MM-DD"
*
* PARAMETERS:
*        dateFromSqlResult: date as text, as returned from ExecuteSQL function
*
* DEPENDENCIES: none
*
* HISTORY:
*        CREATED on 2012-DEC-05 by Daniel Smith dansmith65@gmail.com
* ============================================================================
*/

Let ( [
    ~dateFromSqlResult = Trim ( dateFromSqlResult ) ;
    ~year = Left ( ~dateFromSqlResult ; 4 ) ;
    ~month = Middle ( ~dateFromSqlResult ; 6 ; 2 ) ;
    ~day = Right ( ~dateFromSqlResult ; 2 )
] ;
    Case (
        Length ( ~dateFromSqlResult ) ≠ Length ( "YYYY-MM-DD" ) ;
        "" ; // invalid parameter: wrong length
        
        Filter ( ~year & ~month & ~day ; "0123456789" ) ≠ ~year & ~month & ~day ;
        "" ; // invalid parameter: contained invalid characters
        
        GetAsNumber ( ~year ) < 1 or GetAsNumber ( ~year ) > 4000 ;
        "" ; // invalid parameter: year out of range
        
        GetAsNumber ( ~month ) < 1 or GetAsNumber ( ~month ) > 12 ;
        "" ; // invalid parameter: month out of range
        
        GetAsNumber ( ~day ) < 1 or GetAsNumber ( ~day ) > 31 ;
        "" ; // invalid parameter: day out of range
        
        Date ( ~month ; ~day ; ~year )
    )
)


/**
* ============================================================================
// Unit test
Let ( [
    // specify output format
    ~outputEvaluate =
        "$output & If ( $expected = $actual ; \"pass \" ; \"-FAIL- \" )"
        & " & $test & \" | \" & Quote ( $expected ) & \" | \" & Quote ( $actual ) & \¶" ;
    
    $test = "expected param" ;
    $value = "2012-12-05" ;
    $expected = Date ( 12 ; 5 ; 2012 ) + 1 ;
    $actual = GetAsSqlDate ( $value ) + 1 ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    $test = "expected param: leading/trailing spaces" ;
    $value = " 2012-12-05 " ;
    $expected = Date ( 12 ; 5 ; 2012 ) + 1 ;
    $actual = GetAsSqlDate ( $value ) + 1 ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    $test = "expected param: different separator" ;
    $value = " 2012/12/05 " ;
    $expected = Date ( 12 ; 5 ; 2012 ) + 1 ;
    $actual = GetAsSqlDate ( $value ) + 1 ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    $test = "invalid param: no leading 0's" ;
    $value = "2012-12-5" ;
    $expected = "" ;
    $actual = GetAsSqlDate ( $value ) ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    $test = "invalid param: invalid date: year" ;
    $value = "0000-12-05" ;
    $expected = "" ;
    $actual = GetAsSqlDate ( $value ) ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    $test = "invalid param: invalid date: year" ;
    $value = "4001-12-05" ;
    $expected = "" ;
    $actual = GetAsSqlDate ( $value ) ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    $test = "invalid param: invalid date: month" ;
    $value = "2012-00-05" ;
    $expected = "" ;
    $actual = GetAsSqlDate ( $value ) ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    $test = "invalid param: invalid date: month" ;
    $value = "2012-13-05" ;
    $expected = "" ;
    $actual = GetAsSqlDate ( $value ) ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    $test = "invalid param: invalid date: day" ;
    $value = "2012-12-00" ;
    $expected = "" ;
    $actual = GetAsSqlDate ( $value ) ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    $test = "invalid param: invalid date: day" ;
    $value = "2012-12-32" ;
    $expected = "" ;
    $actual = GetAsSqlDate ( $value ) ;
    $output = Evaluate ( ~outputEvaluate ) ;
    
    ~null = ""
] ;
    $output
)
// Clean up variables
& Let ( [
    $test = "" ;
    $value = "" ;
    $expected = "" ;
    $actual = "" ;
    $output = "" ;
    ~null = ""
] ;
    ""
)
* ============================================================================
*/

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

    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/342

    Prototype: GetAsSqlDate( dateFromSqlResult )
    Function Author: Daniel Smith (http://www.fmfunctions.com/mid/375)
    Last updated: 06 December 2012
    Version: 1

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

 

 

 

 

 

 

 

Top Tags

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