AnsweredAssumed Answered

Transform date text to date with Let()

Question asked by ndveitch on Dec 1, 2017
Latest reply on Dec 1, 2017 by bigtom


Hi there,

 

Can someone see where I am going wrong in my Let() function. I have a find function that is working nicely, except for dates, so I have added in a catch to make sure that the date is formatted correctly. Users can use either a "." or  "/" to space the dates, and either enter 17 or 2017 for the year. (Side Note: my date settings are set to dd/mm/yyyy as I am in South Africa)

Statement in Data Viewer:

Let (

[

$query = "28.11.2017" ;

$query = Substitute ( $query ; ["." ; ¶ ] ; [ "/" ; ¶ ]  ) ;

~day = GetValue ( $query ; 1 )  ;

~month = GetValue ( $query ; 2 )  ;

~year = GetValue ( $query ; 3 )  ;

~year = Right ( ~year ; 2 ) ;

~year = "20" & ~year

];

"GetAsDate: " &GetAsDate ( ~day & "/" & ~month & "/" & ~year ) & ¶ &

"Date: " & Date ( ~month ; ~day ; ~year ) & ¶ &

"Day: " & ~day  & ¶ &

"Month: " & ~month  & ¶ &

"Year: " & ~year

)

Evaluated result:

GetAsDate: ?

Date: 11/28/2017

Day: 28

Month: 11

Year: 2017

 

I am playing around with the function in data viewer to see what results I get, but things don't seem to add up. If I change the $query to "8.11.2017" then the GetAsDate() works fine, until I go to day 13, then the ? comes back. I added the GetAsDate() to see if I could manipulate the date to work, but the main confusion is the Date() function. I was under the impression that FM would give me back the date as Date( 11 ; 28 ; 2017 ) but because my machine is set to dd/mm/yyyy I would see 28/11/2017 but I am getting 11/28/2017 back which is not correct.

 

In searching for answers, I came across this post, https://community.filemaker.com/message/615912?commentID=615912#comment-615912 and I tried both Johan and Tom's  and they worked perfectly, and they also used the Date (month ; day ; year) like I did, but I kept getting incorrect dates.

 

UPDATE: I have to add, I decided to just run my script to see what the results would be, and even thou in data viewer the date would show as mm/dd/yyyy, when the value was entered into the field it went in as dd/mm/yyyy. So my find request worked, but it was just strange that in data viewer it was showing incorrectly.

Outcomes