2012-12-30 has year of both 2012 and 2013 sql

Josh21
New Contributor II

‎07-26-2021 02:25 AM

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