Problem with date value while passing as a parameter to stored procedure

[Former Member]

Hi,

    When i am passing date value as a parameter to stored procedure it is taking date in the format of "dd/MM/yyyy".

    But i want to take the date format like "MM/dd/yyyy". I have tried many functions.I am unable to solve.Please help me out with this problem.

Thanks and Regards,

Krishna Sandeep.

[edy_simon]

Hi Krishna,

If what you say is true, that is strange. Can you tell me which sap version are you using?

As a work around, I suggest you change your sp2 to accept nvarchar 8 Parameters and pass the yyyyMMdd date format from your program

Regards,

Edy

[edy_simon]

Hi Khrisna,

Can we take a step back ?

It seems like we are going in circle.

Tell me if I am wrong :

1. You would Run SP1. The result of this SP1 is an Object of type Date.

2. You pass this result into SP2, which also accepts parameters of type Date. - We have cover this and you had succesfully pass the date into the SP2.

3. SP2 would run in server side.

4. SP2 should return you a value.

5. You would use the result of this SP2 either to show to user /  next processing.

Can you tell me where you want the format to be "MM/dd/yyyy" as in your original question ?

Regards

Edy

[Former Member]

Edy,

     1.I have run sp1.The result of sp1 is datetime(Result is: 2013-01-01 00:00:00.000)

     2.The result value of sp1 to be passed to sp2. Here....

               When i run sp2 @FDATE is accepting date in "mm/dd/yyyy"

          a. If sp2 parameter(@FDATE) is of DATETIME then the date value is not converting to                "mm/dd/yyyy".

          b. If sp2 parameter(@FDATE) is of DATE then the date value has converted into "mm/dd/yyyy".                But sp2 is not executing.

[edy_simon]

Hi Krishna,

In case 2.a. Is there any error message?

Regards,

Edy

[Former Member]

Hi Edy,

       No error. But when passing the value it is accepting date in "dd/mm/yyyy".There comes the error.

[edy_simon]

Hi,

I am sorry,

What do you mean by no error, there comes the error?

Can you take time to explain more?

The more detail you can explain, the more we can help you.

Regards,

Edy

[Former Member]

Edy,

          In case 2a. when i am passing the result value of sp1 to sp2, value  is accepting date in           "dd/mm/yyyy". I want in "mm/dd/yyyy"

          Where as in case 2b. when i am passing the same value to sp2 there it has converted into "mm/dd/yyyy".But stored procedure is not executing and it is showing exception without a message.

[edy_simon]

Hi Khrisna,

Can you tell me why it has to be mm/dd/yyyy?

You know that whether it is jan 31, 2013 or 31 Jan 2013, or 1/31/2013 or 31/1/2013, as long as they are of type date, SQL server knows it is the same value, dont you?

Unless you are telling me that you are passing Jan 3 2013, your SP is receiving march, 1 2013?

Regards,

Edy

[Former Member]

Hi Edy,

          Yes.I am passing Jan 3 2013, SP is receiving march 1 2013.

[Former Member]

I always convert input dates to format "YYYY-MM-DD" and pass it as a string. This is SQL Server's native format, and it's always accepted regardless user's locale and regional settings.

Beni.

[Former Member]

Hi Beni,

          I have converted to format "YYYY-MM-DD" and passed the value to string.But still i am getting the format like "dd/mm/yyyy".Please help me out.

[edy_simon]

Hi,

Can you please explain more specific.

How did you convert to "YYYY-MM-DD" string.

Can you post how you call the SP?

Where did you get the format "dd/mm/yyyy"?

Regards

Edy

[Former Member]

Hi Edy,

           I have pasted a sample code.Please check it out.

           This is the first stored procedure.After execution of stored procedure, i will get "TDate" value.

Dim rsGetDates As SAPbobsCOM.Recordset

                        rsGetDates = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

                        rsGetDates.Command.Name = "AIT_SALARY_GET_FDATE_TDATE"

                        rsGetDates.Command.Parameters.Item("@mon").Value = oForm.Items.Item("17_U_Cb").Specific.Value

                        rsGetDates.Command.Parameters.Item("@yr").Value = oForm.Items.Item("18_U_E").Specific.Value

                        rsGetDates.Command.Execute()

This is the second stored procedure.

rsSalDedns.Command.Name = "AIT_SALARY_DEDUCTIONS"

rsSalDedns.Command.Parameters.Item("@TDATE").Value = Format(rsGetDates.Fields.Item("TDate").Value, "MM/dd/yyyy")---The problem arises here.

rsSalDedns.Command.Execute()

In 2nd procedure i am passing the 1st procedure "TDate" value.While passing the date value after converting in 2nd procedure,again it is taking in the format "dd/mm/yyyy".

[edy_simon]

Hi Krishna,

1. Can you debug and confirm that the result of the first SP is an object of type date ?

2. What is the expected object type of parameter @TDATE in the second SP ? , can you make it an NVARCHAR(8) ?

And call it using

rsSalDedns.Command.Parameters.Item("@TDATE").Value = string.format("{0:yyyyMMdd}",rsGetDates.Fields.Item("TDate").Value)

Regards

Edy

[Former Member]

Hi Edy,

           I tried with that one.But it is taking time instead of date.

[edy_simon]

Hi Krishna,

What is taking time ?

string.format("{0:yyyyMMdd}",rsGetDates.Fields.Item("TDate").Value) should return you date string in the format of 4digits of Year 2 digits of Month 2 digits of date.

Unless your rsGetDates.Fields.Item("TDate").Value is not returning you the correct date object.

Regards

Edy

[Former Member]

Hi Edy,

          I have got date in string format what u have said.But when i passed value to stored procedure parameter there it is taking time.

[edy_simon]

Hi Khrisna,

If you are using sql server 2005, change

string.format("{0:yyyyMMdd}",rsGetDates.Fields.Item("TDate").Value)

To

string.format("{0:yyyyMMdd HH:mm:ss}",rsGetDates.Fields.Item("TDate").Value)

If you are using sql server 2008 above

You can set your parameter to be of data type Date instead of DateTime.

Regards,

Edy

[Former Member]

Hi Edy,

           After converting DateTime to Date i got the format in "mm/dd/yyyy". But this time when i passed date value in "mm/dd/yyyy" to stored procedure, again it is showing error near date and the stored procedure is not executing.

[Former Member]

Hi Krishna,

I think I've missunderstood you. When ecxatally do you want the date format to be "mm/dd/yyyy" - as an output for the customer or inside the sp?

Beni.

[Former Member]

Hi Beni,

          Inside(input) to sp.

[edy_simon]

Hi Krishna,

Can you post your SP signature here?

Just the Declaration of your SP with the expected params.

And do you know which SQL statements throwing the error ?

Regards

Edy

[Former Member]

1st sp

ALTER PROCEDURE <sp1 name>

@mon as varchar(20),

@yr as int

AS

BEGIN

     SET NOCOUNT ON;

    DECLARE @Month int

    DECLARE @Year int

    set @Month = (select code from [@ait_month] where name=@mon)

    set @Year = (select @yr)

    select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) as FDate,DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) as TDate

END

2nd sp

ALTER PROCEDURE ..<sp2 name>......

@FDATE AS DATE,

@TDATE AS DATE,

After execution of 1st sp,it's values to be passed to 2nd sp FDATE,TDATE

[edy_simon]

Hi Khrisna,

Try this :

In your customization :

        Dim rsSalDedns As SAPbobsCOM.Recordset = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        rsSalDedns.Command.Name = "SP2"
        rsSalDedns.Command.Parameters.Item("@TDATE").Value = CType(rsGetDates.Fields.Item("TDate").Value, System.DateTime)
        rsSalDedns.Command.Parameters.Item("@FDATE").Value = CType(rsGetDates.Fields.Item("TDate").Value, System.DateTime)
        rsSalDedns.Command.Execute()

In your SP :

Alter PROCEDURE SP2    
@FDATE AS DATETIME,
@TDATE AS DATETIME
AS
BEGIN
SELECT @FDATE, @TDATE
END

Reason is, Command.Parameters must accepts the exact object type.

It is either you change them all to string. or Change them all to DateTime.

Date is not working because Visual Studio only have data type DateTime.

Regards

Edy

[Former Member]

Hi Edy,

           I tried with what you have given.

           I used CType() function.

           I have converted into "mm/dd/yyyy" format.

In the following line i have passed the value:

rsSalDedns.Command.Parameters.Item("@TDATE").Value = String.Format("{0:MM/dd/yyyy}", CType(rsGetDates.Fields.Item("TDate").Value, System.DateTime))         

After passing the value into sp, again it is converting into "dd/mm/yyyy".

[edy_simon]

Hi,

I think we have a misunderstanding here.

Why do you care about the format?

The format will depend on the default windows format.

As long as it is a date you can do anything on it.

If you want to show it as a different format, then convert it into any format you need in your SP.

Maybe i dont get what you are trying to say?

Regards,

Edy

[Former Member]

Hi Edy,

         I have converted in my format.After passing the value again it is converting to different format.Can u help me out?

[edy_simon]

Hi Krishna,

I am not sure why you want it on a specific format.

So long as processing point of view, date is a date, no matter what the format is.

If you need to show it to your user under specific format you need to convert it into NVARCHAR, see here.

If the format you need is not in the above link, You will need to split up all the Year, Month, and Date values and concatenate them to your liking.

Regards

Edy

[Former Member]

Hi Edy,

          I have changed my data type of date to NVARCHAR. But i am unable to pass value to record set.How to pass a value to record set? Please help me out..