fm2sql.dataType ( )

Function stats

Average user rating
37
359
9999
Support
FileMaker 7.0 +
Date posted
30 September 2013
Last updated
02 January 2014
Version
Recursive function
No

Author Info
 Fabrice

74 functions

Average Rating 4.4

author_avatar



 

Function overview

Prototype

fm2sql.dataType  ( _data;   _type )


Parameters

_data  


_type  


Description

Tags:  Sql   data type  

fm2sql.dataType ( "1/1/2013" ; "DATE" ) => '2013-1-1'
fm2sql.dataType ( "3,14159" ; "NUMBER" ) => 3.14159 (period as separator)
fm2sql.dataType ( "Hello World" ; "TEXT" ) => 'Hello World'

Examples

Sample input

fm2sql.dataType ( "1/1/2013" ; "DATE" )
fm2sql.dataType ( "3,14159" ; "NUMBER" )
fm2sql.dataType ( "Hello World" ; "TEXT" )


Sample output

'2013-1-1'
3.14159 (period as separator)
'Hello World'

 

Function code

/*
fm2sql.dataType ( _data ; _type )
by Fabrice Nordmann, 1-more-thing
http://www.1-more-thing.com

Translates FileMaker Data into SQL
_dataType takes DATE, TIME, NUMBER, TIMESTAMP, TEXT

fm2sql.dataType ( "1/1/2013" ; "DATE" ) => '2013-1-1'
fm2sql.dataType ( "3,14159" ; "NUMBER" ) => 3.14159 (period as separator)
fm2sql.dataType ( "Hello World" ; "TEXT" ) => 'Hello World'

v.1.1 Jan 2013
    handles empty values for dates and timstamps
v.1 Aug 2012

*/

Let ( _s = Left ( _type ; 1 ) ;
Case (
    _s = "D" ; Let ( _data = GetAsDate ( _data ) ; "'" & Case ( _data ; Year ( _data ) & "-" & Month ( _data ) & "-" & Day ( _data )) & "'" ) ;
    _type = "TimeStamp" or _type = "TS" ; Let ( _data = GetAsTimestamp ( _data ) ; "'" & Case ( _data ; Year ( _data ) & "-" & Month ( _data ) & "-" & Day ( _data ) & " " & GetAsTime ( _data )) & "'") ;
    _type = "TEXT" ; "'" & GetAsText ( _data ) & "'" ;
    _s = "T" ; "'" & GetAsTime ( _data ) & "'" ;
    _s = "N" ; Substitute ( _data ; [ "," ; "." ]) ;
    "'" & GetAsText ( _data ) & "'"
)
)

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

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

    Prototype: fm2sql.dataType( _data; _type )
    Function Author: Fabrice (http://www.fmfunctions.com/mid/37)
    Last updated: 02 January 2014
    Version: 1.1

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