SQL Server : DATETIME to INT

Question

The date I have is 10.01.2022, the value I want is 10012022 - how can I do that? Thanks

Answer 1

If you have a DateTime value and want to convert it to an int value with that format, you could cast it to varchar first using a format that gets you close, and then get rid of any unwanted symbols, and then convert to int

declare @d date = '20220110'
select convert(int, replace(convert(varchar, @d, 103), '/', ''))

Answer 2

I would suggest something like this...

Select  Day(YourColumn) * 1000000 + Month(YourColumn) * 10000 + Year(YourColumn)
From    YourTable

It's not clear from your question if you want month-day-year or day-month-year. Regardless, with this code it should be fairly easy to adjust it to meet your situation.

PS. This will be faster than converting to varchar and then to int.