Jeff Moden (8/7/2012)
GSquared (8/7/2012)
Date formatting is done in the front-end application, not in the database. What application are you using? That's the point I'm making.There is no formatting of dates inside the database. You HAVE to change the user-interface (application) to change the format. There is no other way to do what you want. Hence my question, what application are you viewing dates in?
I could be wrong but SET DATEFORMAT appears to do what Vinu wants. Also, although I agree that you shouldn't send formatted dates to a GUI, there are plenty of good reasons to want to format the output of dates in a stored procedure.
SET DATEFORMAT doesn't do what's wanted here, which is display the dates in a different format than the standard YYYY-MM-DD HH:MM:SS.MMM in the client. You have to change UIs, or change settings in the UI (if that's an option), in order to do that.
Try this:
CREATE PROC dbo.ShowADate
AS
SET DATEFORMAT DMY; -- Different format
SELECT GETDATE();
GO
EXEC dbo.ShowADate;
GO
DROP PROC dbo.ShowADate;
Result in SSMS? "2012-08-08 08:37:38.293"
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon