I am such a big fan of ISO 8601 that I write all my dates as YYYYMMDD. The folks at my bank never blinked twice. I have also talked to an organization that hosts international conferences and demonstrated the wisdom of stating their dates as YYYY/MM/DD. This not only to promote understanding but to give all the attendees a level of "shared pain".

But so what? The original poster never made any claims for readability but as a performance booster. Since only the encoding function was presented I assumed that this was to be used in an index. I did not think that you could index on the result of a function. That might be cool if you could. I thought that SQL Server could only index on a column (or more than one).

Vignesh do you have any tests that show the performance increase? I would be interested in looking at that.

The only other reason that I can see for something like this is since the Datetime format uses a singed integer for the date portion that there might be some issues with dates before 1900 being viewed as higher than dates after that as the earlier dates would be negative. That might cause something if your dates are both sides of 1900. I guess that since that nobody relies on the sequence of data coming out of an index that all such issues are well hidden by ORDER BY.

As to DATEPART I can see where that might get slowed down by repeated calls. Might a better way be to convert the Datetime to an ISO 8601 string and return that as an integer? Two functions, one line, and no variables at all. 😉