February 27, 2013 at 2:45 am
Comments posted to this topic are about the item Date Format
March 11, 2013 at 3:21 am
A very good function however it seems to miss the leading zero off dates that are in the first 9 days of the month when using a format of 'dd/mm/yy' although the code seems to try to manage that out.
March 11, 2013 at 6:33 am
Fear Naught (3/11/2013)
A very good function however it seems to miss the leading zero off dates that are in the first 9 days of the month when using a format of 'dd/mm/yy' although the code seems to try to manage that out.
I just tested this out and it seems to work when i run the function. I did however come across an issue in it where, when the month is March or May, it would throw the function off since it recognizes the M in the month name. I have made the fix, see below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnFormatDate] (@Datetime DATETIME, @Format VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @DateValue VARCHAR(32)
SET @DateValue = @Format
IF (CHARINDEX ('YYYY',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'YYYY',
DATENAME(YY, @Datetime))
IF (CHARINDEX ('YY',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'YY',
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX ('Month',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'Month',
DATENAME(MM, @Datetime))
IF (CHARINDEX ('MON',@DateValue)>0)
SET @DateValue = REPLACE(@DateValue, 'MON',
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX ('Mon',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'Mon',
LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX ('MM',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue,'MM',
RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX ('M',@DateValue) > 0) AND (CHARINDEX('MA',@DateValue)=0)
SET @DateValue = REPLACE(@DateValue,'M',
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX ('DD',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'DD',
RIGHT('0'+DATENAME(DD, @Datetime),2))
IF (CHARINDEX ('D',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue,'D',
DATENAME(DD, @Datetime))
RETURN @DateValue
END
March 11, 2013 at 8:50 am
Thanks for the prompt reply. The issue I get is as follows:
select [dbo].[fnDateFormat](getdate()-3,'dd/mm/yyyy')
-- today is the 11th so the minus 3 is to get a date in single figures.
will return
8/03/2013 and not what I would expect which would be 08/03/2013.
Kevin
March 11, 2013 at 8:54 am
No problem. I just ran the same code and it is formatted correctly. Have you tried debugging the code to see where it is going haywire?
March 11, 2013 at 10:02 am
I thought it might be something to do with the version of SQL Server I am using (2000) so tried it on 2008R2 and get the same result.
I'm not too familiar with debug having run it on my 2008R2 server the code runs OK but fails to apply the correct value to the "dd" or indeed "d" part of the @Datetime variable.
Thanks.
March 11, 2013 at 10:04 am
Can you show me the function as it is on your system? I wonder if maybe when you copied and pasted from the article maybe it missed or added something that is skewing the results.
March 11, 2013 at 10:09 am
ALTER FUNCTION [dbo].[fnDateFormat] (@Datetime DATETIME, @Format VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @DateValue VARCHAR(32)
SET @DateValue = @Format
IF (CHARINDEX ('YYYY',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'YYYY',
DATENAME(YY, @Datetime))
IF (CHARINDEX ('YY',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'YY',
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX ('Month',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'Month',
DATENAME(MM, @Datetime))
IF (CHARINDEX ('MON',@DateValue)>0)
SET @DateValue = REPLACE(@DateValue, 'MON',
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX ('Mon',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'Mon',
LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX ('MM',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue,'MM',
RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX ('M',@DateValue) > 0) AND (CHARINDEX('MA',@DateValue)=0)
SET @DateValue = REPLACE(@DateValue,'M',
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX ('DD',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'DD',
RIGHT(0+DATENAME(DD, @Datetime),2))
IF (CHARINDEX ('D',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue,'D',
DATENAME(DD, @Datetime))
RETURN @DateValue
END
March 11, 2013 at 10:18 am
Ok I see the problem. In the code you function lost the single quotes around the 0.
IF (CHARINDEX ('DD',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'DD',
RIGHT(0+DATENAME(DD, @Datetime),2))
If you place single quotes around the 0 that should do the trick.
IF (CHARINDEX ('DD',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'DD',
RIGHT('0'+DATENAME(DD, @Datetime),2))
March 11, 2013 at 10:58 am
Dohhh. Many thanks for your help. I couldn't see the wood for the trees obviously.
A really useful function.
March 11, 2013 at 11:00 am
No problem. Thank you for your comments.
March 11, 2013 at 11:04 am
Here's a similar function with more options that is an inline tvf rather than scalar. The benefits of that should be obvious. I didn't write the original code (and can't remember who did), but I did add quite a few of the options and turned it into an itvf.
CREATE FUNCTION [dbo].[itvfFormatDateWithMask]
(
@date AS DATETIME
,@format_string VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
/*
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'YYYY-MM-DD HH:mm:ss.fff')
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMMM Dx, YYYY hh:mmtt')
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMM Dx, YYYY')
'YYYY' - full year with century
'YY' - year without century
'Y' - last digit of year
'MMMM' - month name
'MMM' - month name first 3 chars only
'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
*/
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)) AS RowNum
FROM
fourRows AS A
CROSS JOIN fourRows AS B
CROSS JOIN fourRows AS C
ORDER BY 1
)
,tokenizedString
AS (
SELECT
N
,REPLACE(C,SUBSTRING(CAST(C AS NVARCHAR(50)),CHARINDEX('""',CAST(C AS NVARCHAR(50)),1),CHARINDEX('""',CAST(C AS NVARCHAR(50)),2)),'ZZZZZZZ') AS 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
(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 'MMM' THEN LEFT(DATENAME(month,@date),3)
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
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)')
AS FormattedDate
)
March 14, 2013 at 4:37 am
This is a really simple and effective script!
One question I had is how would the function know the difference between 'Mon' and 'MON' if the DB collation is case-insensitive?
When I ran the following code on my case-insensitive DB it still gave me a 6...
SELECT CHARINDEX('Mon','YYYY-MON-D')
March 14, 2013 at 8:49 am
In its current format it does not recognize the difference. If you chance the line
IF (CHARINDEX ('MON',@DateValue)>0)
SET @DateValue = REPLACE(@DateValue, 'MON',
LEFT(UPPER(DATENAME(MM, @Datetime)),3)) with
IF (CHARINDEX ('MON',@DateValue COLLATE LATIN1_GENERAL_CS_AS)>0)
SET @DateValue = REPLACE(@DateValue, 'MON',
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
That will force the function to distinguish between the upper case and the lower case. Tested and works on my database.
Thank you for your comment and question.
April 18, 2013 at 7:43 pm
There is a bug in DateFormat function
E.g.: SELECT dbo.fnDateFormat('5/5/2013','MON')
O/P: 5AY
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply