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.

 

[MikeBronner]

Try this:

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

I'm guessing it might not see it due to the missing alias. We use this method on our Oracle database all the time and it works. What database system are you running against?

Take Care,
Mike

 

[EwS]

Thanks for your help. I'm using SQL Server CE database.
It looks like to_char is not supported on compact framework (I typed 'to_char' in help and I got some results for .NET framework, but not compact framework...)
Is there any other way to do this?

 

[ca8msm]

If you are using a DataGrid with bound columns, you should be able to use the DataFormatString. e.g.

<asp:BoundColumn DataField="MyDate" HeaderText="Date Header" DataFormatString="{0:d}"></asp:BoundColumn>

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ThatRickGuy]

Try this:

CONVERT(nvarchar(10), date, 101)

On the MSDN:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_convert.asp

-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]

ca8msm,
I'm not using bound columns. I'm able to change the column width as follows:

myGrid.TableStyles(strDatasetName).GridColumnStyles(0).Width = 0

so maybe there's a way to format the contents of a column? I can't figure out how to do this.

ThatRickGuy,
When I do this:

SELECT CONVERT(nvarchar(10), date, 101) FROM myTable

I don't get any error, but I still get the date like:

4/5/2005

instead of

04/05/05

 

[ca8msm]

Sorry about that - thought I was posting in the ASP.NET forum!

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ThatRickGuy]

Damnit Ca8msm, read the sig!

-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.

 

[ca8msm]

I didn't post a question - I posted an answer!

Maybe you need to amend you sig so you account for people like me as well!!!

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[MikeBronner]

Take a look at the GetDateTimeFormat() function. It is supported by the compact framework and might lead to something you can use. Hopefully this will yield some results for you.


Take Care,
Mike

 

[EwS]

MichaelBronner,
From what I understand, I cannot use GetDateTimeFormat() inside the SELECT statement, and I don't know how else to format the contents of the Date column in my grid other than at the time when the data is selected from the table.

 

[ThatRickGuy]

Well, looks like you might need to just use substring and make your own format.

-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.

 

[MikeBronner]

Have you tried

SELECT CONVERT(CHAR(10), date, 101) AS date FROM table

?

Take Care,
Mike

 

[MikeBronner]

A somewhat more cumbersome method:

SELECT DATEPART(m, date) | '/' | DATEPART(d, date) | '/' |  DATEPART(yy, date) AS date FROM table

Note: you might want to replace the '|' with '&'. Not sure if TSQL works the some way with concatenating strings as PLSQL.

Take Care,
Mike

 

[SqueakinSweep]

Heres a useful SQL function I came across for manipulating dates into custom formats.

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)

    -- dddd --
    SET @outStr = REPLACE(@outStr, 'dddd',
        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 --
    SET @outStr = REPLACE(@outStr, 'ddd',
        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 --
    SET @outStr = REPLACE(@outStr, 'dd', SUBSTRING(@datestr,1,2))
    -- d --
    SET @outStr = REPLACE(@outStr, 'd', CONVERT(int,SUBSTRING(@datestr,1,2)))
    -- 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 (This is last because it put letters back into the @outStr and if done previously, things like the 'h' in 'March' become an hour --
    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

    -- 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, 'am/pm', @meridian+'m')
    -- a/p --
    SET @outStr = REPLACE(@outStr, 'a/p', @meridian)
    
    RETURN @outStr

END

Sweep
...if it works dont mess with it

 

[EwS]

MichaelBronner,
I tried:

SELECT CONVERT(nvarchar(10), date, 101) FROM myTable

(as ThatRickGuy suggested earlier) and it didn't make any difference.

I also tried the following (with | and &):

SELECT DATEPART(m, date) | '/' | DATEPART(d, date) | '/' |  DATEPART(yy, date) AS date FROM myTable

and I got an error.

SqueakinSweep,
It looks like a useful function, but how can I make it work in compact framework?

I found this code in .NET help, but the overloaded version of the DataGridTextBoxColumn constructor used here is not supported on compact framework:

   ' Add style for 'Date' column.
   Dim myDateDescriptor As PropertyDescriptor = myCurrencyManger.GetItemProperties()("Date")
   ' 'G' is for MM/dd/yyyy HH:mm:ss date format.
   Dim myDateStyle = New DataGridTextBoxColumn(myDateDescriptor, "G")
   
   myDateStyle.MappingName = "Date"
   myDateStyle.HeaderText = "Date"
   myDateStyle.Width = 150
   myTableStyle.GridColumnStyles.Add(myDateStyle)

 

[ThatRickGuy]

EwS, put Sweep's function on your SQL Server and use it in your SQL statement/stored proc.

-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,
The thing is that I'm using SQL Server CE on the device, but the database that I use on the desktop is not SQL Server...

 

[ThatRickGuy]

Well, use Sweep's func in the SQL Server CE, and then tell us what your other DB engine is.

-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.

 

[SBendBuckeye]

Nice function sweep, star worthy! ews, I think the concatenation operator in Sql Server is ||, not |.

Have a great day!

j2consulting@yahoo.com