Joining dates

[has75042]

Hey All,

I need to find out how many orders were place FY 2019. We have a calendar dim, but somehow I can't connect my source table to calendar dim.

I have changed the table date format to match the calendar dim format, but I still can't get FY2019 field to work.

Source Table Date Format :

4/1/2018 12:00:00 AM

Calendar Dim Date Format:

2/2/2012

the below expression I am using to change the date format

date( `time_res`, 'MM/DD/YYYY') as time_res,

Any help would be really appreciated

Thanks,

[tincholiver]

Hey boddy, try this:

Date(Floor(Date#(date, 'D/M/YYYY h:m:ss')), 'DD/MM/YYYY') as time_res;

[pradosh_thakur · Accepted solution]

Hi

the first thing you need to check is your time stamp and date are in date/time format or string?

if they are in timestamp/date format then

for source table date(floor(timestamp_field)) will do

if they are in string then you need to first change them to timestamp then change it to date

date(floor(timestamp#(timestamp_field,'MM/DD/YYYY hh:mm:ss tt')))

In the above i have assumed your dates are in MM/DD/YYYY format, you may nee to change that if otherwise.

Thanks

Pradosh

[Marcos_rv]

yep, as it says tincholiver, you have to use the function floor to make join between dates.

This is because in qv the dates are a number and the date format is a mask, for example:

date (40032.5123, 'dd-mm-yyyy hh: mm: ss')
= 07-17-2009 12:17:42

The numbers that are after the period (.) Represent the hours, minutes and seconds.

So if you had to relate 2 formats, only with the function date could not, since it is a mask, you would have to extract the decimals.

example: date (floor (40032.5123)) = 07-17-2009

regards!!!!