Get Date from Text timestamp

[infoweb]

Hi there,

 

I am trying to extract a date from an imported timestamp but not having any success.

 

Here's what I have :

 

A timestamp field where I import a timestamp from an external source. The timestamp is formatted as follows:

 

SaleDate = 2015-03-13 07:50:16

 

I am trying to set a variable using SetVariable [$Date; Value:GetAsDate ( Sales::SaleDate )]

 

But all I get is ?

 

How exactly do I convert that to a date only field?

 

I tried LeftWords to extract the date part only, but filemaker still doesn't recognise that as a valid date. my system is set to dd/mm/yyyy So I guess I will have to use a number of text functions to rebuild the date in the correct format.

 

Is there any other simpler option?

 

I'd rather avoid using text based functions incase the formats vary.

[comment]

my system is set to dd/mm/yyyy So I guess I will have to use a number of text functions to rebuild the date in the correct format.

Your guess is entirely correct. Try =

Date ( Middle ( SaleDate ; 6 ; 2 ) ; Middle ( SaleDate ; 9 ; 2 ) ; Left ( SaleDate ; 4 ) )

 

Is there any other simpler option?

Not really.

 

I'd rather avoid using text based functions incase the formats vary.

 

Well, formats are not supposed to "vary". That's why we have them. A format is the agreed way to transmit data from one system to another. The receiving system relies upon the transmission conforming to the agreed format - otherwise things just won't work.

 

If you want your target system to anticipate changes in the transmitted format, you will have to build some degree of intelligence into it (by stringing together even more text manipulations). And no degree of intelligence - neither human nor artificial - will be able to tell you if "1/2/2015" is January 2 or February 1.

[infoweb]

Thanks comment:

 

I ended up going with :

 

Right ( LeftWords ( sale_date ; 1 ) ; 2 ) & "-" & Middle ( LeftWords ( sale_date ; 1 ) ; 6 ; 2 ) & "-" & Left ( sale_date ; 4 )

 

Does adding the: Date (  ) function make a difference?

 

for example can I just use:

 

Right ( LeftWords ( sale_date ; 1 ) ; 2 ) & "-" & Middle ( LeftWords ( sale_date ; 1 ) ; 6 ; 2 ) & "-" & Left ( sale_date ; 4 )

 

Or is there a difference if I used:

 

Date ( Right ( LeftWords ( sale_date ; 1 ) ; 2 ) & "-" & Middle ( LeftWords ( sale_date ; 1 ) ; 6 ; 2 ) & "-" & Left ( sale_date ; 4 ) )

 

I can see overall your solution is neater than mine, I'll probably switch it over.

 

thanks for the input.

[comment]

for example can I just use:

 

Right ( LeftWords ( sale_date ; 1 ) ; 2 ) & "-" & Middle ( LeftWords ( sale_date ; 1 ) ; 6 ; 2 ) & "-" & Left ( sale_date ; 4 )

 

if you do that, you will end up with a text result of "13-03-2015". If you then set the result type of the calculation field to Date, it will do the equivalent of =

GetAsDate ( "13-03-2015" )

which will work for as long as your file is set to use the dd-mm-yyyy format. That's why using the Date() function is preferable - it will work the same no matter what the locale settings are.

 

 

Or is there a difference if I used:

 

Date ( Right ( LeftWords ( sale_date ; 1 ) ; 2 ) & "-" & Middle ( LeftWords ( sale_date ; 1 ) ; 6 ; 2 ) & "-" & Left ( sale_date ; 4 ) )

 

Well yes, there is a big difference: that wouldn't work at all. The Date function requires 3 parameters (all of them numbers) and the order of the parameters is always (month; day; year)