Extract only date from a DateTime column asp.net

Question

in the SqlDataSource, I have specified a SelectCommand that retrieves a date:

SelectCommand="SELECT [training_id], Convert(varchar(14),[trainingDate],101) FROM [tbl_training]"

However DateGenerated gives me a value like 11/04/2011 12:00:00 AM. How do I extract only 11/25/2011 using SelectCommand?

Any help would be appreciated.

Answer 1

The value retrieved as a DateTime will always include a time, because that's how the type is defined. You can:

• Use the Date property to ensure you get a DateTime value at midnight (e.g. for equality calculations)
• Ignore the time part when formatting for the user. To format a DateTime to only display the date in a culture-sensitive way, you should use the "d" or "D" standard date/time format specifier. Don't hard-code a custom date/time format string unless you're sure that's what your users expect. (11/4/2011 looks like April 11th to me, for example...)

How else do you want to use the value? While I think it's a fundamental flaw that DateTime is used for several different things, you should be able to work around it if you're careful.

Answer 2

Don't specify a size, try

Convert(varchar,[trainingDate],101)

If you were to run this on SQL Server for today you would get:

Declare @datey datetime
Set @datey = GETDATE()
Select CONVERT(varchar, @datey, 101) as [DateOnly]

Result: 11/04/2011

Its also worthwhile to note in SQL Server Denali all these magic numbers for formatting will go away with the new FORMAT() function