0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Murat Çiçek
  • 1
  • 2
  • https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15 – Nathan_Sav Jan 11 '22 at 13:43
  • 3
    Why not store the value as a `date`? It is a date after all. Neither `varchar` or `int` are suitable data types for storing date and/or time data. – Thom A Jan 11 '22 at 13:50
  • Do you mean you have a datetime value and want to convert it to an int in that specific format ? Or is it the opposite ? – GuidoG Jan 11 '22 at 13:53
  • 2
    Is that October 1st or January 10th? See why ambiguous, regional formats are bad? – Aaron Bertrand Jan 11 '22 at 18:04

2 Answers2

0

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), '/', ''))
GuidoG
  • 11,359
  • 6
  • 44
  • 79
0

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.

George Mastros
  • 24,112
  • 4
  • 51
  • 59