I am trying to obtain the month and year in the format of "MM-YYY", then "YYY" to get a values such as 12-2012. I noticed an error where a timestamp of 2012-12-30T00:00:00.000+0000 results in both 12-2013 and 2013. This is an error, since 2012-12-30 is not the last day of that year, it is 2012-12-31. The correct result should be 12-2012 and 2013. I would like to know how to solve this error and what caused it.
My select statement is as such:
select Date,cast(Date as TIMESTAMP),
date_format(cast(Date as TIMESTAMP), 'MM-YYY'),
date_format(cast(Date as TIMESTAMP), 'YYY')
from SalesFactsDate1
where date_format(cast(Date as TIMESTAMP), 'YYY')=2012
or date_format(cast(Date as TIMESTAMP), 'YYY')=2013
order by Date
Date is in string format, but it can be cast as a timestamp
