Relation with date fields

[user-azadi]

Hello all

i have two table (Table_voucheritem , Table_Taghvim) that relationed together

with VoucherDate.in data model view , two tables have relation together ,

but due to different format (date) they can't join together correctly.

i want they join together until when i click on VoucherDate's listbox ,

find Equivalent and show persian_date.

[effinty2112]

Hi Mohammad,

You have the key field VoucherDate formatted differently in your tables. When loading the table Table_voucheritem, format VoucherDate like

Date(Floor(Timestamp(VoucherDate,'DD/MM/YYYY hh:mm:ss')),'DD.MM.YYYY') as VoucherDate

Good luck

Andrew

[Kushal_Chawda]

Covert both the Dates with same format

Table_voucheritem:

LOAD Credit, Debit, GacSubsidiaryAccountRef, date(floor(VoucherDate)) as VoucherDate

FROM Table_VoucherItem.qvd (qvd);

Table_Taghvim:

LOAD QV_DATE_NUM,

          date(floor(GREGORIAN_DATE)) as VoucherDate,

   PERSIAN_DATE, Year_Shamsi, Month_Shamsi, Day_Shamsi

FROM Miladi2Shamsi.xlsx (ooxml, embedded labels, table is persian_date);

[user-azadi]

Dear Andrew , Thanks a lot... , That worked

[passionate · Accepted solution]

Instead of using date field to join two table

use numeric value of Date to join table.

[effinty2112]

HI Mohammad,

Glad to have been of help!

Andrew

[user-azadi]

Dear Kushal

your script doesn't work ,

however thank you for your help...

[effinty2112]

Hi Mohammad,

If your satisfied that you have a solution please mark the correct answer.

Kind regards

Andrew

[user-azadi]

Dear Andrew

i have this syntax:

----------------------------------------------

SQL  

SELECT

   Year

  ,Month

  ,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

,sum(Credit) - sum(Debit) as mandeh

      , Date(Floor(Timestamp(item.VoucherDate,'DD/MM/YYYY hh:mm:ss')),'DD.MM.YYYY') as VoucherDate

FROM

----------------------------------------------

when i run script , don't identify Timestamp function in that (because sql haven't Timestamp function)

what must i to do?