Makedate Date extract

[nareshthavidishetty]

Hi,

I have used make date to extract date from year and month fields in table Fact2 and finally we concatenated  this to Fact1.

But when we are having Same dates twice.

Please check the attachment.

Logic: Date(MonthEnd(MakeDate(Left(Calendar_Year&''&Calendar_Month, 4), Right(Calendar_Year&''&Calendar_Month,2))), 'DD-MM-YYYY') as  [Calendar Date]

Thanks..

[Or]

If you are getting the same value twice, you probably have the same value (or a value that generates the same dates with this logic) in your underlying data. You can probably solve this by using DISTINCT, but it would be better to find out why you're getting duplicates by checking the underlying data.

[jonathandienst · Accepted solution]

For some reason, you seem to be getting two different date values with the same format. Do you have another source for some portion of this field? MonthEnd will result in a time component of 23:59:59.999. Perhaps the other source has a different time component (or no time). When formatted as dd-MM-yyyy, the time component is not displayed, but the underlying value is different to the value created by MonthEnd.

Check your sources, and be sure to use Floor() to remove the time component. Use this for your posted date,

Date(Floor(MonthEnd(Date#(Calendar_Year & '-' & Calendar_Month, 'yyyy-MM'))), 'DD-MM-YYYY') as [Calendar Date]

And be sure to add Floor() to the other date expressions that feed into [Calendar Date] as well unless they have no time component (like a date in a master calendar).

[nareshthavidishetty]

Thanks a lot Jonathan.

The point is very Interesting.