Date View Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date View Error

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-11 : 07:23:05

Hello there,

I had created a function in my DB:

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    IF (CHARINDEX ('YYYY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YYYY', DATENAME(YY, @Datetime))
    IF (CHARINDEX ('YY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YY', RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX ('Month',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Month', DATENAME(MM, @Datetime))
    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
       SET @StringDate = REPLACE(@StringDate, 'MON',
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX ('Mon',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Mon', LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('MM',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'MM',
                  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX ('M',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'M',
                        CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX ('DD',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'DD',
                         RIGHT('0'+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX ('D',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'D', DATENAME(DD, @Datetime))   
RETURN @StringDate
END
GO
Source: [url]http://www.sqlusa.com/bestpractices2005/centurydateformat/[/url]

Now when am trying to execute the following T-SQL, result showing in different way:

SELECT dbo.fnFormatDate ('1/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('2/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('3/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]
union all
SELECT dbo.fnFormatDate ('4/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('5/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]
union all 
SELECT dbo.fnFormatDate ('6/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('7/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('8/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('9/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]
union all 
SELECT dbo.fnFormatDate ('10/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('11/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]
union all 
SELECT dbo.fnFormatDate ('12/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]

See wherever I declared Error Column as 'Yes', the Date showing like the following: 3arch 21, 2011; 5ay 21, 2011; Septe9ber 21, 2011; Nove11ber 21, 2011 and 21ece12ber 21, 2011.

Can you guide me regarding this error?

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-11 : 07:52:25

SQLUSA, Hmmm...

quote:

Originally posted by daipayan

Hello there,

I had created a function in my DB:

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    IF (CHARINDEX ('YYYY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YYYY', DATENAME(YY, @Datetime))
    IF (CHARINDEX ('YY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YY', RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX ('Month',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Month', DATENAME(MM, @Datetime))
    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
       SET @StringDate = REPLACE(@StringDate, 'MON',
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX ('Mon',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Mon', LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('MM',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'MM',
                  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX ('M',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'M',
                        CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX ('DD',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'DD',
                         RIGHT('0'+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX ('D',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'D', DATENAME(DD, @Datetime))   
RETURN @StringDate
END
GO
Source: [url]http://www.sqlusa.com/bestpractices2005/centurydateformat/[/url]

Now when am trying to execute the following T-SQL, result showing in different way:

SELECT dbo.fnFormatDate ('1/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('2/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('3/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]
union all
SELECT dbo.fnFormatDate ('4/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('5/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]
union all 
SELECT dbo.fnFormatDate ('6/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('7/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('8/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('9/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]
union all 
SELECT dbo.fnFormatDate ('10/21/2011', 'Month DD, YYYY') as [Date], ' ' as [Error]
union all
SELECT dbo.fnFormatDate ('11/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]
union all 
SELECT dbo.fnFormatDate ('12/21/2011', 'Month DD, YYYY') as [Date], 'Yes' as [Error]

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 07:53:23

My first advice is to not use SQLUSA for anything but laughs. He is looking for ocurrences of D or M and replacing them. There goes March,May,December -- any month with a D or M in it won't work. The only reason January works is because he used YY or YYYY and not just a single Y. Formatting is best done in the front end, but if you must do it in SQL, I am sure you can find a better function, or write one yourself.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources