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 Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 2:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:52 AM
Points: 224, Visits: 336
Comments posted to this topic are about the item Date Format
Post #1424416
Posted Monday, March 11, 2013 3:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:10 PM
Points: 16, Visits: 65
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.
Post #1429119
Posted Monday, March 11, 2013 6:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:52 AM
Points: 224, Visits: 336
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
Post #1429228
Posted Monday, March 11, 2013 8:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:10 PM
Points: 16, Visits: 65
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
Post #1429313
Posted Monday, March 11, 2013 8:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:52 AM
Points: 224, Visits: 336
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?
Post #1429315
Posted Monday, March 11, 2013 10:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:10 PM
Points: 16, Visits: 65
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.
Post #1429366
Posted Monday, March 11, 2013 10:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:52 AM
Points: 224, Visits: 336
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.
Post #1429368
Posted Monday, March 11, 2013 10:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:10 PM
Points: 16, Visits: 65
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

Post #1429372
Posted Monday, March 11, 2013 10:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:52 AM
Points: 224, Visits: 336
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))
Post #1429376
Posted Monday, March 11, 2013 10:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:10 PM
Points: 16, Visits: 65
Dohhh. Many thanks for your help. I couldn't see the wood for the trees obviously.

A really useful function.
Post #1429404
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse