Is there a way in which I can set the date format in Sql server management while designing a table? I want the date of a column to be in the format "dd/mm/yyyy".
SQL Server stores dates in an internal numeric format - not in a date format. You don't need to worry about the format of the data in the database - only when you select data from the table for display purposes.
If you are selecting from the database for a downstream application - don't convert the column to a specific format, that downstream system will understand the date/datetime format and you can then use the PC's regional settings to determine the output format.
If you just want to display the date - then use CONVERT in your query and select the format you prefer...
Note: the above format you are using is subject to misinterpretation - depending on the systems regional settings.
No its not possible to change the date format. Only you can do convert things to generic "string" datatypes. No matter whatever you use it will still not return Date Time value.
Thank you both.
Just to join the bandwagon, if you develop the habit of storing dates/times as formatted character-based columns, you will end up hating yourself for the rest of the time you have any association with said columns. Store the data using the correct date/time datatype. Only produce a formatted date for result sets. And don't use the newer FORMAT function to do such a thing. It's 44 times slower than CONVERT even on simple formats.
1 Like