Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Change the default date format in sqlserver 2005 Expand / Collapse
Author
Message
Posted Sunday, July 27, 2008 10:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 3:37 AM
Points: 369, Visits: 161
Hi All
How can I change the date format in my sql server 2005?
i.e
select getdate()
it gives 2007-09-03 18:27:06.463
But I want to change it to say 03-09-2007.

I need to change the format into dmy..


Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
Post #541602
Posted Sunday, July 27, 2008 10:52 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
Dates are not stored in a format - this is controlled when you query the database and can be set using DATEFORMAT. The other option is to use CONVERT and specify the format in the convert command.

You can find more information on both in BOL (Book Online).


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #541604
Posted Sunday, July 27, 2008 10:59 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 3:37 AM
Points: 369, Visits: 161
Hi williams..

I agree with u..

But whats my friend's requirement is to change the settings which attained by sql server 2005..

by default it is coming like

SET DATEFORMAT mdy;
select getdate()

Result:
2008-07-28 10:26:25.920

Can I able to change it in to Date/month/Year format


Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
Post #541607
Posted Monday, July 28, 2008 8:44 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
You can use

SET DATEFORMAT dmy;

Or, you can use CONVERT in the select statement. Date formats are usually better controlled in the client.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #541948
Posted Wednesday, August 6, 2008 4:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2008 6:48 PM
Points: 35, Visits: 174
it is default comes directly from instalation
Post #547367
Posted Wednesday, September 10, 2008 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 2:31 PM
Points: 5, Visits: 87
To set the date format according to your need you need to execute the following SP on master database:

EXEC sp_configure 'default language', '23';

'23' stands for the Code of the format details of which you can get by running

select * from syslanguages

After execution of this stored proc it will ask you to Run the RECONFIGURE statement to install.

then Run

RECONFIGURE;

let me know if it works for you

Post #566928
Posted Wednesday, November 18, 2009 6:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 18, 2009 6:28 AM
Points: 1, Visits: 0
Great reply... thanks for you wonderful answer.. I got the same problem.. Because of you i got released from that worry.. thank you so much saket
Post #820770
Posted Thursday, November 19, 2009 7:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:48 AM
Points: 889, Visits: 1,732
Try this:

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS MyDate

I hope it will be helpful for you.
Post #821588
Posted Wednesday, October 30, 2013 1:57 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:33 PM
Points: 457, Visits: 476
Along this line about the Default datetime value display (in SQL Server 2005) I do not understand why if I declare a variable as a datetime type and set it to the value returned from the GetDate() function I get back a format the same as the above (i.e., 2013-10-30 14:29:40.710), however when returning a datetime type from a user defined function call it is displayed as "Oct 10 2013 2:29PM" since there are no conversions or SET DATEFORMAT involved why is this second format automatically chosen for display?

Ron K.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
Post #1509932
Posted Wednesday, October 30, 2013 3:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
Ron Kunce (10/30/2013)
Along this line about the Default datetime value display (in SQL Server 2005) I do not understand why if I declare a variable as a datetime type and set it to the value returned from the GetDate() function I get back a format the same as the above (i.e., 2013-10-30 14:29:40.710), however when returning a datetime type from a user defined function call it is displayed as "Oct 10 2013 2:29PM" since there are no conversions or SET DATEFORMAT involved why is this second format automatically chosen for display?



My guess would be that it's because different people wrote different sections of code in SQL Server. There are two bottom lines here, though.

1st and unless there is no front-end involved, you shouldn't worry about formatting dates in SQL Server. Let the front-end take care of that so that regional settings can kick in.

2nd, if you absolutely must format dates in SQL Server for display or, perhaps, output to a file, then you should absolutely and explicitly control the date format using CONVERT. Trusting default date formats is kind of like trusting the "natural sort order" of a query... when you least expect it, it will change.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1509961
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse