Matching the date format of a field imported from csv

[#1]

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

[Anonymous]

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

[swuehl]

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).

[#4 · Accepted solution]

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

[swuehl]

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).