Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to match the format of 2 date fields 1 from a resident table and the second one from a csv file.
The first date from the resident table is calculated using the date(date#(WeekEnd(now(),3,-2)))) as myDate
Second date comes from the csv file with the following expression.
LOAD date(date#(myDate)) as myDate from $(myPath)$(vSonPlan).csv (txt, delimiter is ',');
When I try joinint the tables I based on the date the join doesn't work because the fields are not formated the same. I get 2 rows for the same date. 1 from the 1st table 2nd from the second.
Could anyone help??
Thanks
Sorry guys it was actually correct. It turns out I wasn't using the date() function in part of the if statement. so some of the dates were coming with the wrong format please ignore the question
hi bhancerli,
ur date formats for both date must b varying
Use date function n make it perfect
Ex: date(myDate),'DD-MM-YY')
date(date#(WeekEnd(now(),3,-2)),'DD-MM-YY')
regards,
Vaibhav
I think your problem is not the format (you format both times using date(), so format should be your standard date format), but that weekend is returning an underlying numeric value describing the last millisecond of that weekend day. So you probably need to strip off the time part of your resident table date (you can check this by displaying the numeric value: num(myDate) ).
date(daystart(WeekEnd(now(),3,-2))) as myDate
(You don't need to apply date#() function on Weekend(), because it's already returning a timestamp).
Sorry guys it was actually correct. It turns out I wasn't using the date() function in part of the if statement. so some of the dates were coming with the wrong format please ignore the question
Ok, no problem. Just take care that your numeric values match if you want to link these fields as key fields (i.e. if using weekend / yearend, take care of the internal numeric representation when linking to pure date fields).