Datagrid: display date and not date & time.

[m0nty005]

I've datetime values retrieved from SQL Server as mm/dd/yyyy. I put it in a dataset and displayed it in the datagrid.. it shows up as mm/dd/yyyy 12:00:00 AM. Why can't i get it so the dgrid show mm/dd/yyyy? I could set the datagrid to AUTOGENERATECOLUMNS="FALSE" and format myDate column as {0:MM/dd/yyyy}, but I do not want to set the AUTOGENERATECOLUMS to false cuz there are alot of other data i want. Is there any other way to refine/reformat the data type on the asp.net end for values in the datagrid?

 

[Veep]

I have yet to let a DataGrid AutoGenerate (in production) it's columns. The reason for this is control of the output. The only way to go is Bound or Template columns if you want complete control over what's happening on your grid. Having said that, if you're really happy with your grid the way it is then the solution is in your SQL. If you're calling a Stored Procedure or just using an inline SQL statement you can manage the delivered output via the SQL... From BOL (you'll need to look at BOL to see the code that will solve your problem):

CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax
Using CAST:

CAST ( expression AS data_type )

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments
expression

Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.

data_type

Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

The same goes for the JET db Engine (although you need to use JET syntax).

 

[checkai]

You could do this in SQL

Convert(char(10), datefield,101) as datefieldName

However, I would not AutoGenerate your columns...and if you don't, you can select the string format to be "{0:d}"

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."