Menu
I need to change the date format from US (mm/dd/YYYY) to UK (dd/mm/YYYY) on a single database on a SQL server machine.
How can this be done?
I've seen statements that do this for the whole system, and ones that do it for the session, but I can't change the code now as it will have to go through QA again, so I need a quick fix to change the date time format.
Update
I realize that the date time has nothing to do with how SQL Server stores the data, but it does have a lot to do with how it parses queries.
I'm chucking raw data from an XML file into a database. The dates in the XML file are in UK date format.
501
The default date format of SQL is mdy(U.S English). Now to change sql server default date format from “mdy”(mm/dd/yyyy) to “dmy”(dd/mm/yyyy),we have to use SET DATEFORMAT command. Before changing the default date time format of sql server lets go through the way to know what format of date time is currently in use.
We change the date format from one format to another. For example - we have stored date in MM-DD-YYYY format in a variable, and we want to change it to DD-MM-YYYY format. We can achieve this conversion by using strtotime() and date() function.
Default output format SQL Server outputs date, time and datetime values in the following formats: yyyy-mm-dd, hh:m:ss. nnnnnnn (n is dependent on the column definition) and yyyy-mm-dd hh:mm:ss.
You could use SET DATEFORMAT, like in this example
declare @dates table (orig varchar(50) ,parsed datetime) SET DATEFORMAT ydm; insert into @dates select '2008-09-01','2008-09-01' SET DATEFORMAT ymd; insert into @dates select '2008-09-01','2008-09-01' select * from @dates You would need to specify the dateformat in the code when you parse your XML data
141
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
