Format date inside the SELECT statement 3

[EwS]

I'm using compact framework. After I execute this statement:

SELECT date FROM myTable

I use the Fill method of the SqlCeDataAdapter to bind the result set to my grid. However, I want the data in a certain format. I tried this, but it doesn't work:

SELECT to_char(date,'MM\DD\YY') FROM myTable

I think that for formatting the date (which is used strictly for display purposes) it shouldn't matter in what format the data is stored in the SQL CE database, right?
Thank you.

 

[EwS]

ThatRickGuy,
I don't think you can define a function in SQL CE...
My other database is a .soa file that's part of a Mas 90 system.

SBendBuckeye,
Using || instead of | didn't work.

 

[ThatRickGuy]

If all else fails, load the raw data from SQLCE/MAS90 to a data set. Loop through the dataset, Reformat the data, and add it to a new dataset. Or format the output instead of the data.

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[EwS]

ThatRickGuy,
Looping through the data set would certainly work (I just have to ensure it won't make things slower...).

 

[techsmith]

Squeekinsweep,

I thought the idea of a sql function to format a date was worth investigating. Unfortunately, there were some scenarios that weren't handled by your code. I've tweaked the function to handle these, and here is the finished article.

We'll be using this a lot so thanks for posting it:

CREATE FUNCTION fx_formatdatetime  (@datetime datetime, @formatstr varchar(255))  
RETURNS varchar(255)
AS
BEGIN 

    -- This Function works similar to the Format function in Visual Basic for creating Custom Formating Strings for Date/Time Variables
    -- Valid characters for the @formatstr Are...
    -- d        Displays the day as a number without a leading zero (1 - 31)
    -- dd        Displays the day as a number with a leading zero (01 - 31)
    -- ddd        Displays the day as an abbreviation (Sun - Sat)
    -- dddd        Displays the day as a full name (Sunday - Saturday)
    -- w        Displays the day of the week as a number (1 for Sunday through 7 for Saturday)
    -- m        Displays the month as a number without a leading zero (1 - 12)
    -- mm        Displays the month as a number with a leading zero (01 - 12)
    -- mmm        Displays the month as an abbreviation (Jan - Dec)
    -- mmmm    Displays the month as a full month name (January - December)
    -- yy        Displays the year as a 2-digit number (00-99)
    -- yyyy        Displays the year as a 4-digit number (1000 - 9999)
    -- q        Displays the quarter of the year (1 - 4)
    -- h        Displays the hour as a number without leading zeros (0 - 23)
    -- hh        Displays the hour as a number with leading zeros (00 - 23)
    -- th        Displays the hour as a number without leading zeros (1 - 12)
    -- n        Displays the minute as a number without leading zeros (0 - 59)
    -- nn        Displays the minute as a number with leading zeros (00-59)
    -- s        Displays the second as a number without leading zeros (0 - 60)
    -- ss        Displays the second as a number with leading zeros (00 - 60)
    -- am/pm    Displays am before noon; Displays pm after noon through 11:59 P.M.
    -- a/p        Displays a before noon; Displays p after noon through 11:59 P.M.
    --    Examples (assuming a date of March 7th, 2003 at 8:07:05 A.M.)
    --    @formatstr        Returns
    --    m/d/yy            3/7/03
    --    mmmm d, yyyy        March 7, 2003
    --    mm-dd-yyyy h:nnam/pm    03-07-2003 8:07am

    DECLARE @outStr    varchar(255)
    DECLARE @datestr    varchar(24)
    DECLARE @meridian    varchar(1)
    DECLARE @temp    varchar(2)
    
    SET @outStr = @formatstr
    SET @datestr = CONVERT(varchar(24), @datetime, 113)
	--d fix--
    SET @outStr = REPLACE(@outStr, 'd', 'x')
	--am/pm--
    SET @outStr = REPLACE(@outStr, 'am/pm', 'zz')
	--a/p--
    SET @outStr = REPLACE(@outStr, 'a/p', 'z')
    -- w --
    SET @outStr = REPLACE(@outStr, 'w', DATEPART(dw,@datetime))
    -- yyyy --
    SET @outStr = REPLACE(@outStr, 'yyyy', SUBSTRING(@datestr,8,4))
    -- yy --
    SET @outStr = REPLACE(@outStr, 'yy', SUBSTRING(@datestr,10,2))
    -- q --
    SET @outStr = REPLACE(@outStr, 'q', DATEPART(q,@datestr))
    -- hh --
    SET @outStr = REPLACE(@outStr, 'hh', SUBSTRING(@datestr,13,2))
    -- th --
    IF CONVERT(int,SUBSTRING(@datestr,13,2)) > 12
    SET @outStr = REPLACE(@outStr, 'th', CONVERT(int,SUBSTRING(@datestr,13,2)) - 12)
    ELSE SET @outStr = REPLACE(@outStr, 'th', SUBSTRING(@datestr,13,2))
    -- h --
    SET @outStr = REPLACE(@outStr, 'h', CONVERT(int,SUBSTRING(@datestr,13,2)))
    -- nn --
    SET @outStr = REPLACE(@outStr, 'nn', SUBSTRING(@datestr,16,2))
    -- n --
    SET @outStr = REPLACE(@outStr, 'n', CONVERT(int,SUBSTRING(@datestr,16,2)))
    -- ss --
    SET @outStr = REPLACE(@outStr, 'ss', SUBSTRING(@datestr,19,2))
    -- s --
    SET @outStr = REPLACE(@outStr, 's', CONVERT(int,SUBSTRING(@datestr,19,2)))

    -- m, mm, mmm, mmmm
    IF CHARINDEX('m',@outStr,0) > 0 BEGIN
        IF CHARINDEX('mm',@outStr,0) > 0 BEGIN
            IF CHARINDEX('mmm',@outStr,0) > 0 BEGIN
                IF CHARINDEX('mmmm',@outStr,0) > 0 BEGIN
                    SET @outStr = REPLACE(@outStr, 'mmmm',
                        CASE DATEPART(mm, @datetime)
                            WHEN 1 THEN 'January'
                            WHEN 2 THEN 'February'
                            WHEN 3 THEN 'March'
                            WHEN 4 THEN 'April'
                            WHEN 5 THEN 'May'
                            WHEN 6 THEN 'June'
                            WHEN 7 THEN 'July'
                            WHEN 8 THEN 'August'
                            WHEN 9 THEN 'September'
                            WHEN 10 THEN 'October'
                            WHEN 11 THEN 'November'
                            WHEN 12 THEN 'December'
                        END)
                END
                ELSE SET @outStr = REPLACE(@outStr, 'mmm', SUBSTRING(@datestr,4,3))
            END
            ELSE BEGIN
                SET @temp = DATEPART(mm,@datetime)
                IF (DATEPART(mm,@datetime)<10) SET @temp = '0' + @temp
                SET @outStr = REPLACE(@outStr, 'mm', @temp)
            END

        END
        ELSE BEGIN
            SET @outStr = REPLACE(@outStr, 'm', DATEPART(mm,@datetime))
            SET @outStr = REPLACE(@outStr, 'a'+CAST(DATEPART(mm,@datetime) AS varchar(1))+'/p'+CAST(DATEPART(mm,@datetime) AS varchar(1)),'am/pm')
        END
    END
	--dddd already replaced with xxxx--
	SET @outStr = REPLACE(@outStr, 'xxxx',
	    CASE DATEPART(dw, @datetime)
	        WHEN 1 THEN 'Sunday'
	        WHEN 2 THEN 'Monday'
	        WHEN 3 THEN 'Tuesday'
	        WHEN 4 THEN 'Wednesday'
	        WHEN 5 THEN 'Thursday'
	        WHEN 6 THEN 'Friday'
	        WHEN 7 THEN 'Saturday'
	    END)
	--ddd already replaced with xxx--
	SET @outStr = REPLACE(@outStr, 'xxx',
	    CASE DATEPART(dw, @datetime)
	        WHEN 1 THEN 'Sun'
	        WHEN 2 THEN 'Mon'
	        WHEN 3 THEN 'Tue'
	        WHEN 4 THEN 'Wed'
	        WHEN 5 THEN 'Thu'
	        WHEN 6 THEN 'Fri'
	        WHEN 7 THEN 'Sat'
	    END)
	--dd already replaced with xx--	     
	SET @outStr = REPLACE(@outStr, 'xx', SUBSTRING(@datestr,1,2))
	--d already replaced with x--
	SET @outStr = REPLACE(@outStr, 'x', CONVERT(int,SUBSTRING(@datestr,1,2)))

    -- Used by am/pm and a/p --
    IF CONVERT(int,SUBSTRING(@datestr,13,2)) > 12
    SET @meridian = 'p'
    ELSE SET @meridian = 'a'
    -- am/pm --
    SET @outStr = REPLACE(@outStr, 'zz', @meridian+'m')
    -- a/p --
    SET @outStr = REPLACE(@outStr, 'z', @meridian)
    
    RETURN @outStr

END

 

[PC888]

If you are using standard SQL, your first form should work using the TO_CHAR(date,"MM/DD/YY") format.

However, since you have used a back-slash ("\"), make sure it was not set as the escape character. To confirm this, try your statement using the forward slash or a hyphen.

Hope this helps.

 

[EwS]

PC888,
"/" and "-" didn't work.