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 12»»

Date Format Problem Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 4:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 1,118, Visits: 1,582
Hi,

I have a little problem where my Database stores Dates in 'yy/mm/dd' format.

I want to retrieve Date in 'dd/mm/yy' Format.

I know that this is possible by converting the Date to Varchar as follows:

Select Convert(Varchar(10), GetDate(), 103) As Date

But, I want the Datatype to remain as 'Date' and give me the Date in the format 'dd/mm/yy'. Can this be done??

Thanks And Regards,


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1340508
Posted Monday, August 6, 2012 6:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Dates in SQL Server aren't stored in any of those formats. They're stored as a number of days since "day 0" (1/1/1900 by default). The formatting you are seeing is done by the application layer, whether that's SSMS or something else.

For example, today (6 August 2012), on my dev copy of SQL 2008 R2, is stored as 41125, since that's the number of days since 1 Jan 1900.

Try this:

SELECT  CAST(GETDATE() AS INT),
DATEDIFF(DAY, 0, GETDATE());

If you want the display to be different, change the settings in your application, or use a Convert mask for it.


- 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
Post #1340561
Posted Monday, August 6, 2012 6:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:07 AM
Points: 1,260, Visits: 3,545
what is the collation and data type of the field storing the current date?


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1340563
Posted Monday, August 6, 2012 10:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 1,118, Visits: 1,582
calvo (8/6/2012)
what is the collation and data type of the field storing the current date?


Collation: SQL_Latin1_General_CP1_CI_AS
DataType: DateTime

GSquared (8/6/2012)
Dates in SQL Server aren't stored in any of those formats. They're stored as a number of days since "day 0" (1/1/1900 by default). The formatting you are seeing is done by the application layer, whether that's SSMS or something else.

For example, today (6 August 2012), on my dev copy of SQL 2008 R2, is stored as 41125, since that's the number of days since 1 Jan 1900.

Try this:

SELECT  CAST(GETDATE() AS INT),
DATEDIFF(DAY, 0, GETDATE());

If you want the display to be different, change the settings in your application, or use a Convert mask for it.


I can use a Conversion Function to convert the Dat Format to the way I want but the resultant Date that I would have is a Varchar String and not a Date. If I try to conver/Cast it back to date it changes back to the same Date Format.

The problem above is still a problem that I would love to get some help on.

But, the Date and Time Format didn't matter in what I was trying to do. I have done it with using the same Format. But, I would still like to have some opinions from the Gurus if I can change the format of the Date without changing the Data Type.

Thank you very much for the replies.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1340990
Posted Monday, August 6, 2012 11:00 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
vinu512 (8/6/2012)
calvo (8/6/2012)
what is the collation and data type of the field storing the current date?


Collation: SQL_Latin1_General_CP1_CI_AS
DataType: DateTime

GSquared (8/6/2012)
Dates in SQL Server aren't stored in any of those formats. They're stored as a number of days since "day 0" (1/1/1900 by default). The formatting you are seeing is done by the application layer, whether that's SSMS or something else.

For example, today (6 August 2012), on my dev copy of SQL 2008 R2, is stored as 41125, since that's the number of days since 1 Jan 1900.

Try this:

SELECT  CAST(GETDATE() AS INT),
DATEDIFF(DAY, 0, GETDATE());

If you want the display to be different, change the settings in your application, or use a Convert mask for it.


I can use a Conversion Function to convert the Dat Format to the way I want but the resultant Date that I would have is a Varchar String and not a Date. If I try to conver/Cast it back to date it changes back to the same Date Format.

The problem above is still a problem that I would love to get some help on.

But, the Date and Time Format didn't matter in what I was trying to do. I have done it with using the same Format. But, I would still like to have some opinions from the Gurus if I can change the format of the Date without changing the Data Type.

Thank you very much for the replies.


Actually, you already have the answer in the article in the link in your signature line. SET DATEFORMAT DMY or SET DATEFORMAT MDY. It only changes the current query.



--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 #1341001
Posted Tuesday, August 7, 2012 6:17 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
vinu512 (8/6/2012)
calvo (8/6/2012)
what is the collation and data type of the field storing the current date?


Collation: SQL_Latin1_General_CP1_CI_AS
DataType: DateTime

GSquared (8/6/2012)
Dates in SQL Server aren't stored in any of those formats. They're stored as a number of days since "day 0" (1/1/1900 by default). The formatting you are seeing is done by the application layer, whether that's SSMS or something else.

For example, today (6 August 2012), on my dev copy of SQL 2008 R2, is stored as 41125, since that's the number of days since 1 Jan 1900.

Try this:

SELECT  CAST(GETDATE() AS INT),
DATEDIFF(DAY, 0, GETDATE());

If you want the display to be different, change the settings in your application, or use a Convert mask for it.


I can use a Conversion Function to convert the Dat Format to the way I want but the resultant Date that I would have is a Varchar String and not a Date. If I try to conver/Cast it back to date it changes back to the same Date Format.

The problem above is still a problem that I would love to get some help on.

But, the Date and Time Format didn't matter in what I was trying to do. I have done it with using the same Format. But, I would still like to have some opinions from the Gurus if I can change the format of the Date without changing the Data Type.

Thank you very much for the replies.


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?


- 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
Post #1341185
Posted Tuesday, August 7, 2012 4:44 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
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.


--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 #1341605
Posted Wednesday, August 8, 2012 1:46 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 AM
Points: 75, Visits: 443
SET DATEFORMAT is responsible for how character strings are interpreted when converted to datetime. It's not resposible for the way db stores datetime values, and not responsible for how these values are displayed in ssms or somewhere else.
Using it in such way may lead to error. For example:
--let's say we have some important date hardcoded in out query
set dateformat ymd;
declare @SomeImportantDate datetime = '1999-02-01';
select @SomeImportantDate; -- SSMS displays it like '1999-02-01 00:00:00.000'
select datename(mm,@SomeImportantDate) + ' ' + datename(dd,@SomeImportantDate); -- and it is: February 1
go
set dateformat ydm;
declare @SomeImportantDate datetime = '1999-02-01'; --the same initialization
/*
Now SSMS displays it like '1999-01-02 00:00:00.000', so we may think that we
changed display style somehow...
*/
select @SomeImportantDate;
-- but lets check - is it still the same date
select datename(mm,@SomeImportantDate) + ' ' + datename(dd,@SomeImportantDate); -- and it is: January 2
/*
No, it is another date, and the query logic might be corrupted,
we just influenced on how @SomeImportantDate variable was initialized
That's why to avoid such thing it's recommended to use independent formats for datetime like:
'19990201' or (with time portion) '1999-01-02T00:00:00.000'
*/
go

So, as GSquared said, it is client responsibility to display date in specific format. When you run query in SSMS and see smth like - '1999-01-02 00:00:00.000' - it is not sql server, it is how SSMS displays dates.



I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Post #1341710
Posted Wednesday, August 8, 2012 6:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1341821
Posted Wednesday, August 8, 2012 10:34 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
SomewhereSomehow (8/8/2012)
SET DATEFORMAT is responsible for how character strings are interpreted when converted to datetime. It's not resposible for the way db stores datetime values, and not responsible for how these values are displayed in ssms or somewhere else.


Yep... I know that there's only one way to store DATETIME values. My problem, apparently, was that I got SET DATEFORMAT and setting the language seriously confused and I didn't verify it . My apologies on this.


--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 #1342017
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse