0

How to convert getdate() to the format dd/mm/yyyy while keeping the datatype date and not varchar? This is what I tried

SELECT cast(CONVERT(varchar(12), GETDATE(), 103) as date)

but it is wrong.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Autumn Sunflower
  • 1
  • 2
  • 5
    You don't. The date and time data types have no format; they are binary values. If you want a format, then you sort that out in your presentation layer, not SQL Server. – Thom A Sep 01 '20 at 10:07
  • "it is wrong" - why? show the output and explain why that is not what you want. this seems like a very unclear question, since it's not evident how a date can have a format. – underscore_d Sep 01 '20 at 10:23
  • [For the 1024 time – DateTime has no format!](https://zoharpeled.wordpress.com/2019/12/19/for-the-1024-time-datetime-has-no-format/) – Zohar Peled Sep 01 '20 at 13:05

1 Answers1

1

The date datatype doesn't have a concept of a format. It's just a moment in time perhaps with a timezone attached but without any particular fixed representation. When you format it, it becomes a varchar. If you cast the varchar back to a date, it loses the format. This will be pretty much the same in any programming language that has a native date datatype (or date/datetime objects). It's not specific to SQL.

If you want a particular string representation of a date, then you want a varchar, so don't try to cast it back to a date.

This seems to be what's called an XY problem. What is wrong with CONVERT(varchar(12), GETDATE(), 103)? Yes, it's a varchar, but what's why are you unsatisfied with it being a varchar?

tobyink
  • 13,478
  • 1
  • 23
  • 35
  • Since relationnel databases (1970 Codd / 1974 Ingres / 1979 Oracle...) are not COBOL's file, every datatype is a real data type and not a character string. The maner you see the date or time is only the consequence of the application you use to views the data.... – SQLpro Sep 01 '20 at 10:16