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


Date Format


Date Format

Author
Message
zulmanclock
zulmanclock
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 472
Comments posted to this topic are about the item Date Format
Fear Naught
Fear Naught
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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.
zulmanclock
zulmanclock
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 472
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
Fear Naught
Fear Naught
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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
zulmanclock
zulmanclock
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 472
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?
Fear Naught
Fear Naught
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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.
zulmanclock
zulmanclock
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 472
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.
Fear Naught
Fear Naught
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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


zulmanclock
zulmanclock
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 472
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))
Fear Naught
Fear Naught
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 65
Dohhh. Many thanks for your help. I couldn't see the wood for the trees obviously.

A really useful function.
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