SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Format Problem


Date Format Problem

Author
Message
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3615 Visits: 1626
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 ;-)
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56945 Visits: 9730
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
calvo
calvo
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3258 Visits: 4018
what is the collation and data type of the field storing the current date?

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3615 Visits: 1626
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 ;-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212327 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56945 Visits: 9730
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212327 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SomewhereSomehow
SomewhereSomehow
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 469
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
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56945 Visits: 9730
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212327 Visits: 41977
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 Blush. 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search