cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Josh21
New Contributor II

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

1 REPLY 1

Kaniz_Fatma
Community Manager
Community Manager

Hi @ Josh21! My name is Kaniz, and I'm the technical moderator here. Great to meet you, and thanks for your question! Let's see if your peers on the Forum have an answer to your questions first. Or else I will follow up shortly with a response.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!