August 6, 2012 at 4:47 am
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,
August 6, 2012 at 6:54 am
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
August 6, 2012 at 6:55 am
August 6, 2012 at 10:32 pm
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.
August 6, 2012 at 11:00 pm
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
Change is inevitable... Change for the better is not.
August 7, 2012 at 6:17 am
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
August 7, 2012 at 4:44 pm
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
Change is inevitable... Change for the better is not.
August 8, 2012 at 1:46 am
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.
August 8, 2012 at 6:41 am
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
August 8, 2012 at 10:34 am
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
Change is inevitable... Change for the better is not.
August 8, 2012 at 10:36 am
GSquared (8/8/2012)
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.ShowADateAS
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"
Thanks, Gus. You're right. Not sure why I had such a bad memory on this one but I got seriously confused.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 8, 2012 at 3:28 pm
Could just write a UDF to take your datetime and reformat it. Returning a string, of course.
August 8, 2012 at 6:35 pm
I didn't write this function (though I made some minor mods), but picked it up somewhere along the way. It is a standard tool-box item I use on almost every SQL instance. It let's you format a date anyway you want. The formatting codes are at the bottom and are very specific and case-sensitive. Any character not in the list of codes can be added to the mask in any order.
/*
This function requires a Tally table to work (thanks to Jeff Moden)
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
CREATE TABLE dbo.Tally
(
N INT
,CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
)
DECLARE @Counter INT
SET @Counter = 1
WHILE @Counter <= 11000
BEGIN
INSERT INTO dbo.Tally
(N)
VALUES
(@Counter)
SET @Counter = @Counter + 1
END
*/
CREATE FUNCTION [dbo].[tvfFormatDateWithMask]
(
@date AS DATETIME
,@format_string VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
WITH fourRows(N)
AS (
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
)
,cteTally(N)
AS (
SELECT TOP (50)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
fourRows AS A
CROSS JOIN fourRows AS B
CROSS JOIN fourRows AS C
ORDER BY 1
)
,tokenizedString
AS (
SELECT
N
,C
,groupId = DENSE_RANK() OVER (ORDER BY C, _groupId)
FROM
(
SELECT
N
,SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) AS C
,_groupId = N - ROW_NUMBER() OVER (PARTITION BY SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) ORDER BY N)
FROM
cteTally
WHERE
N <= LEN(@format_string)
) AS fs)
SELECT
formattedDate =
(SELECT
CASE REPLICATE(MIN(C),COUNT(*))
WHEN 'YYYY' THEN RIGHT('0000' + CAST(YEAR(@date) AS NVARCHAR(4)),4)
WHEN 'YY' THEN RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),2)
WHEN 'Y' THEN CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),1) AS INT) AS NVARCHAR(2))
WHEN 'MMMM' THEN DATENAME(month,@date)
WHEN 'MM' THEN RIGHT('00' + CAST(MONTH(@date) AS NVARCHAR(2)),2)
WHEN 'M' THEN CAST(MONTH(@date) AS NVARCHAR(2))
WHEN 'DDDD' THEN DATENAME(weekday,@date)
WHEN 'DD' THEN RIGHT('00' + CAST(DAY(@date) AS NVARCHAR(2)),2)
WHEN 'D' THEN CAST(DAY(@date) AS NVARCHAR(2))
WHEN 'HH' THEN RIGHT('00' + CAST(DATEPART(hour,@date) AS NVARCHAR(2)),2)
WHEN 'H' THEN CAST(DATEPART(hour,@date) AS NVARCHAR(2))
WHEN 'hh' THEN RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
--WHEN 'h' THEN CAST(DATEPART(hour,@date) - (12 * CEILING((DATEPART(hour,@date) - 12) * .1)) AS NVARCHAR(2))
WHEN 'h' THEN
CASE WHEN LEFT(
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2),1) = 0 THEN
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),1)
ELSE
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
END
WHEN 'mm' THEN RIGHT('00' + CAST(DATEPART(minute,@date) AS NVARCHAR(2)),2)
WHEN 'm' THEN CAST(DATEPART(minute,@date) AS NVARCHAR(2))
WHEN 'ss' THEN RIGHT('00' + CAST(DATEPART(second,@date) AS NVARCHAR(2)),2)
WHEN 's' THEN CAST(DATEPART(second,@date) AS NVARCHAR(2))
WHEN 'fff' THEN RIGHT('000' + CAST(DATEPART(millisecond,@date) AS NVARCHAR(3)),3)
WHEN 'f' THEN CAST(DATEPART(millisecond,@date) AS NVARCHAR(3))
WHEN 'tt' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'PM'
ELSE N'AM'
END
WHEN 't' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'P'
ELSE N'A'
END
WHEN 'x' THEN
CASE
WHEN CAST(DAY(@date) AS INT) IN (1,21,31) THEN N'st'
WHEN CAST(DAY(@date) AS INT) IN (3,23) THEN N'rd'
ELSE N'th'
END
ELSE MIN(C)
END
FROM
tokenizedString
GROUP BY
groupId
ORDER BY
MIN(N)
FOR
XML PATH('')
,TYPE
).value('(./text())[1]','nvarchar(50)')
)
/*
SELECT FormattedDate FROM dbo.tvfFormatDateWithMask(GETDATE(),'DD/MM/YY') AS CurrDate
--or
SELECT
FormattedDate
FROM
(
SELECT
GETDATE() AS UnformattedDate
) Result
CROSS APPLY
dbo.tvfFormatDateWithMask(Result.UnformattedDate,'DD/MM/YY')
'YYYY'- full year with century
'YY'- year without century
'Y'- last digit of year
'MMMM'- month name
'MM'- month number with leading zero
'M'- month number without leading zero)
'DDDD'- day name
'DD'- day number with leading zero
'D'- day number without leading zero
'HH'- hour with leading zero (24 hr format)
'H'- hour without leading zero
'hh'- hour with leading zero (12 hr format)
'h'- hour without leading zero (12 hr format)
'mm'- minutes with leading zero
'm'- minutes without leading zero
'ss'- seconds with leading zero
's'- seconds without leading zero
'fff'- milliseconds with leading zeros
'f'- milliseconds without leading zeros
'tt'- AM or PM
't'- A or P
'x'- st, nd, or th
*/
August 8, 2012 at 10:03 pm
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply