Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by zulmanclock » Date Format
Add to briefcase
16 posts, Page 1 of 2
12»»

Date Format Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
zulmanclock
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: Yesterday @ 9:24 AM
Points: 207, Visits: 218
Comments posted to this topic are about the item Date Format
Post #1424416
Fear Naught
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
zulmanclock
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: Yesterday @ 9:24 AM
Points: 207, Visits: 218
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
Fear Naught
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
zulmanclock
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: Yesterday @ 9:24 AM
Points: 207, Visits: 218
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
Fear Naught
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
zulmanclock
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: Yesterday @ 9:24 AM
Points: 207, Visits: 218
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
Fear Naught
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
zulmanclock
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: Yesterday @ 9:24 AM
Points: 207, Visits: 218
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
Fear Naught
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
16 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.