How Date is stored internally in Synapse DB

Occasional Visitor

Hello Everyone,

In which format do Synapse DB stores date?
Is it YYYY-MM-DD or YYYY-MM-DDT00:00:00.000000?
I have seen it storing the date in the latter format? I tried casting it as well but no luck.
So I am guessing it internally stores it in YYYY-MM-DDT00:00:00.000000 format.
Can someone please explain this behavior?

 

Thanks in Advance

1 Reply

Greetings Saurav!

Here is a link to our documentation that shows the different formats based on data type for dates and times - https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact...

It sounds like the real issue you are working on is being able to query the date field. I assume you are using T-SQL when querying the field. If that is true, then I would suggest looking at the datediff function - https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=azure-sqldw-latest

Hope this helps!

Kevin Conan
Sr. Product Manager - Synapse