That’s how SQL Server stores DATE data types. Very cool! Bottom-line: The Formatted Date is For Presentation Purposes Only SELECT DATEDIFF(DAY,CAST('' AS DATE),CAST('' AS DATE)) This is the number of days from January 1, 1 CE. This time, we can’t know the value by looking at it. SELECT CAST(CAST('' AS DATE) AS VARBINARY(3)) If you know a little about hex numbers, you know that this is equal to 1 – it’s 1 day from the starting point, January 1, 1 CE. The bytes are reversed, and the actual hex value is 0x000001. This is a little tricky, but this post gave us an idea. SELECT CAST(CAST('' AS DATE) AS VARBINARY(3)) - January 2, 1 CE It is logical because that’s the minimum DATE value. From this value, we can see that the integer value of January 1, 1 CE is 0. The result is 0x000000 in hexadecimal format. To convert this to an integer, we convert that date to VARBINARY first, like this: SELECT CAST(CAST('' AS DATE) AS VARBINARY(3)) First, the DATE data type minimum value is or January 1, 1 CE or Common Era. It doesn’t mean we can’t know it for sure. Well, Microsoft says it is so but does not provide more information. Let’s take the DATE data type, for example. Does SQL Server store 21:35 as is in the database? How about ? Are they stored as formatted strings? Basing on this documentation from Microsoft, it is not like this. Read on until the end! But before we proceed, let’s take a short detour on how SQL Server stores dates. In this article, we’ll explore the standard to deal with all these varieties and examples.

Let alone users who change it in their stations for personal preferences! With all these scenarios, not dealing with them appropriately is a recipe to break.ĭo your apps deal with different cultures? That’s another level of complexity and a problem when dealing with these various SQL date formats. However, it varies in reports and files for data exchanges and Extract-Transform-Load (ETL) project. But with longer date formats, we interchangeably use day-month-year and month-day-year.Īt work, I never encountered SQL Server date format setting other than month-day-year. Month-day-year is the general format for numeric dates. In the Philippines, we use 2 formats: month-day-year and day-month-year. That’s why the SQL date format is not something we can take easy. Dates can be mixed with time, which is another story. For numeric dates, we have month-day-year, day-month-year, and year-month-day. Thanks to the diversity of cultures on Earth, we have a variety of date formats.

Razorsql date format