Date Format

I have a written a stored procedure and excecuting it and displaying it in gridview(ASP.NET). I am getting Birthdate in the form of8/23/1956 12:00:00 AM. I just need8/23/1956. I have defined it as smalldatetime in stored procedure. How can I get it in that format ?

HELP.......

In your .net code, if the object is of type DateTime you can call ToShortDate.

|||

Either you can convert the datetime to a varchar in your stored procedure:

select convert(varchar(10), getdate(), 103)

... or you can format the output in the GridView:

<asp:BoundFieldDataField="now"DataFormatString="{0:dd/MM/yyyy}"HtmlEncode="false"/>

|||

Set the DataFormatString, either in designer or HTML:

<asp:BoundField DataField="Date" DataFormatString="{0:dd-MMM-yy}" HeaderText="Date" HtmlEncode="false" />

|||

You can read more about CONVERT here:http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx

...and more about DataFormatString here:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx

|||

Thanks a bunch ... millepag

That CONVERT thing worked for me!!!!

Date field cannot be null!

Visual Basic 2005 Professional Edition:

I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.

With this it looks like your table design has the field set not to allow nulls. You will have to alter the table definition to allow nulls for that field. This can be done in raw tsql, or using the table design views in either of the management studio tools.

|||

I went into DataSetDesigner and in Properties I set AllowDbNull to True.

But, when I tried to change the NullValue property from "Throw Exception" to "Empty" or "Nothing" (there are only 3 choices) it said "For columns not defined as System.String, the only valid value is (Throw exception)".

|||

Given that you've just allowed nulls, the value of the NullValue property is irrelevant, as the exception should never be raised.

|||

The following exception occurred in the DataGridView:

System.Data.NoNullAllowedException: Column 'BirthDate' does not allow nulls

I set 'AllowDbNull' to True in the properties field of the DataSet Designer, replied 'Yes' to 'Save Changes', but, when I go back in, 'AllowDbNull' is back to False.

|||

ok - I've done some research on this (I'm a SQL guy, not a VS guy) and it looks like there may be a bug in the Dataset Designer for non-string columns. I found what looks like a workaround here: http://www.codeproject.com/useritems/Bug_fixed_in_DataDesigner.asp

Hope this helps