Jump to content

Get Date from Text timestamp


This topic is 2823 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This topic is 2823 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.