Change format of DateTime variable in C#, Not string output

Question

How can I convert the format of a C# DateTime variable from d/M/yyyy HH:mm:ss to yyyy/M/d HH:mm:ss
I do not want to change a string output, but the DateTime variable itself.

I have a stored procedure with parameter @date in datetime. Now I am trying to pass a DateTime variable Today from C# to this stored procedure. The C# DateTime variable is in format d/M/yyyy HH:mm:ss but the stored procedure needs the format yyyy/M/d HH:mm:ss.

I try to this with LINQ to SQL: code:

public static object SP_SelectSalesPriceItem()
{
    var query = dc.SP_SalesPrice(DateTime.Now);
    return query;
}

Stored Procedure:

ALTER PROCEDURE [dbo].[SP_SalesPrice]
    @Date datetime
AS
BEGIN
    SET NOCOUNT ON;


    select SalesPrice.[Item No_]
    from Sales Price 
    where [Starting Date] < @Date and ([Ending Date] > @Date

This is the Error I get in asp.net: Specified cast is not valid.

Stack Trace:

[InvalidCastException: Specified cast is not valid.]
System.Data.SqlClient.SqlBuffer.get_Int32() +5002837
System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i) +38
Read_SP_SalesPriceResult(ObjectMaterializer1 ) +1477 System.Data.Linq.SqlClient.ObjectReader2.MoveNext() +32
System.Web.UI.WebControls.ListView.CreateItemsWithoutGroups(ListViewPagedDataSource dataSource, Boolean dataBinding, InsertItemPosition insertPosition, ArrayList keyArray) +222
System.Web.UI.WebControls.ListView.CreateChildControls(IEnumerable dataSource, Boolean dataBinding) +1040
System.Web.UI.WebControls.ListView.PerformDataBinding(IEnumerable data) +44
System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +128
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +33
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
System.Web.UI.WebControls.ListView.PerformSelect() +113
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
System.Web.UI.WebControls.ListView.CreateChildControls() +55
System.Web.UI.Control.EnsureChildControls() +102
System.Web.UI.Control.PreRenderRecursiveInternal() +42
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2496

A DateTime has no format. If you want to pass Today anyway, you can also do this in the database directly: dateadd(dd, datediff(dd, 0, getdate()), 0). — Tim Schmelter
What is dc in this code? Also, in the [Sales Price] table, what is the data type of the [Starting Date] and [Ending Date] columns? — Chris Dunaway
Just realised you've not yet told us what the error is! What exception are you actually getting? — tomfanning

Oded Oded · Accepted Answer · 2012-11-14T15:11:32

What you are asking for is not possible for the simple reason that a DateTime does not have a format.

A format is the string representation of a DateTime.

The stored procedure should be taking a DATETIME parameter for @date - if it isn't, you are doing something wrong.

If you are using a NVARCHAR or such type you will need to call ToString on your DateTime in C# with the correct format before adding the parameter value.

Change format of DateTime variable in C#, Not string output

3 Answers