on 02-04-2013 10:23 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
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.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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".
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
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
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
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
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
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".
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
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
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.