none
Binding with char type for the SmallDatetime type of output parameter in SQL Server is returning date in Mon DD YYYY HH:MM(AM/PM) format RRS feed

  • Question

  • Hi There,

    I do have a stored procedure having a Smalldatetime type of output parameter. I am sending datetime value as an input from my application to the SP and also fetching the value set for the parameter inside the SP. I am using ADO.NET to call a stored procedure. Parameter binding is done using SqlDbType.Char and not with the specific datetime type (for a mysterious reason :) ).

    My application is able to send the data correct to the SP and also receiving data from the SP, but with the default conversion style of Smalldatetime to string literal - which is 0.

    So, for example, SP sets parameter value as '03/26/2016 10:30:55' then, my application receives it in form of 'Mar 26 2016 10:30AM'. What I want is to receive the datetime value as specified in the SP and not the converted one. If anyone can help me on this would be much appreciated.

    Wednesday, March 30, 2016 3:52 PM

Answers

  • Hi Himanshu_kus,

    >> if It possible to change the default style used by CAST/CONVERT when converting DATETIME?

    Yes, you can convert your dateTime format in T-SQL. Then as my first reply, you should return a string value from a stored procedure  to display your dateTime format what you want.

    Please also refer to the following link

    https://msdn.microsoft.com/en-us/library/ms187928.aspx?f=255&MSPPError=-2147217396

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 1, 2016 9:12 AM

All replies

  • Hi Himanshu,

    >>SP sets parameter value as '03/26/2016 10:30:55' then, my application receives it in form of 'Mar 26 2016 10:30AM'. What I want is to receive the datetime value as specified in the SP and not the converted one.

    Based on your scenario, I would suggest you return a string value.

    string time = "03/26/2016 10:30:55"; DateTime dt = Convert.ToDateTime(time); Console.WriteLine("Today is " +dt.ToString() + ".");

    By the way, please also note that this dateTime  type is decided by your System setting, here I made a picture as following

    Have a nice day!

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Kristin Xie Thursday, March 31, 2016 3:26 AM
    Thursday, March 31, 2016 3:25 AM
  • Thanks for taking time to answer Kristin.

    But my basic query is that, if It possible to change the default style used by CAST/CONVERT when converting DATETIME? By default it is taking format #0 for conversion while DateTime2 is taking format #121 for conversion. I am looking for the DateTime type to return date in format #121 by default.

    Thursday, March 31, 2016 10:38 AM
  • Hi Himanshu_kus,

    >> if It possible to change the default style used by CAST/CONVERT when converting DATETIME?

    Yes, you can convert your dateTime format in T-SQL. Then as my first reply, you should return a string value from a stored procedure  to display your dateTime format what you want.

    Please also refer to the following link

    https://msdn.microsoft.com/en-us/library/ms187928.aspx?f=255&MSPPError=-2147217396

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 1, 2016 9:12 AM