By Ger Cannoll - 8/1/2012
How do I extract the Date Only from a SF DateBoxe.g. DateTime myDate = DateTime.Today.Date gets Date Only Part But I cant seem to do: DateTime myDatepart = MySfDateBox.Date
|
By Ivan George Borges - 8/1/2012
Hi Ger.
Try
DateTime myDatepart = new DateTime(MySfDateBox.CurrentYear, MySfDateBox.CurrentMonth, MySfDateBox.CurrentDay);
|
By Ger Cannoll - 8/1/2012
Thanks Ivan. Thars worked fine.
regards,
Gerard
|
By Ivan George Borges - 8/1/2012
You're welcome, Ger. 
|
By Ger Cannoll - 8/9/2012
Hi Ivan.
Having another problem with Date/DateTime.
I am trying to pass in a Date to a Stored procedure parameter (set up as date) but get a Conversion Failed in Sql Server
My date = 31 December 2012 (Displayed in UK format with day first)
DateTime DT = new DateTime(this.dateFrom.CurrentYear,this.dateFrom.CurrentMonth, this.dateFrom.CurrentDay);
MessageBox.Show(DT.ToString() ); shows : 31/12/2012 00:00:00 I have tried various combinations of date and Date/time (both on the client side and in the Stored Procdure definition of the date Filed) but cannot seem to get the Sql SProc to run. Any ideas ?
|
By Edhy Rijo - 8/9/2012
Hi Gerard,
In my Stored Procedures I cast the datetime as follow:
@DateFrom DATE,
@DateTo DATE,
@ParkingNumber INT,
@ParkingPlan INT,
@Cashier_Out_UserID VARCHAR(20)
AS
SELECT
*
FROM
PaymentsReport_View p
WHERE
p.PaymentStatus = 1 AND
(CAST(p.DatePaidOn AS DATE) >= @DateFrom AND
CAST(p.DatePaidOn AS DATE) <= @DateTo) AND
(p.ParkingNumber = @ParkingNumber OR @ParkingNumber = 0) AND
(p.ParkingPlan = @ParkingPlan OR @ParkingPlan = -1) AND
(p.Cashier_Out_UserID = @Cashier_Out_UserID OR @Cashier_Out_UserID = '')
ORDER BY
p.DatePaidOn, p.Cashier_Out_UserID
And in SF, I use an SQLCommand object and pass the date as a parameter using bo.DateFieldName.Date
Using cmd As New Data.SqlClient.SqlCommand
cmd.CommandTimeout = 0
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "dbo.spPaymentsReport"
cmd.Parameters.AddWithValue("@DateFrom", rptParams.TransactionDateStart).SqlDbType = Data.SqlDbType.Date
cmd.Parameters.AddWithValue("@DateTo", rptParams.TransactionDateEnd).SqlDbType = Data.SqlDbType.Date
cmd.Parameters.AddWithValue("@ParkingNumber", rptParams.ParkingNumber).SqlDbType = Data.SqlDbType.Int
cmd.Parameters.AddWithValue("@ParkingPlan", rptParams.ParkingPlan).SqlDbType = Data.SqlDbType.Int
cmd.Parameters.AddWithValue("@Cashier_Out_UserID", rptParams.CashierUserID).SqlDbType = Data.SqlDbType.VarChar
BusinessLayer.FillMultipleDataTables(cmd, Me.DataSource.SourceBO)
End Using
|
By Ger Cannoll - 8/10/2012
Hi Edhy. Thanks for your reply.
I am still getting a Sql Exception--> Command failed when converting Date and/or Time from character string. I am initially taking the data from a SF Date Box. My Code is:
this.dateFrom is a SF DateBox
-- Client Code --
DateTime x = new DateTime(this.dateFrom.CurrentYear, this.dateFrom.CurrentMonth, this.dateFrom.CurrentDay);
comm.Parameters.AddWithValue("@DateFrom", x).SqlDbType= SqlDbType.Date;
-- Sql SProc Code --
ALTER PROCEDURE [dbo].[sprSelectSalesFormTrans]
@DateFrom Date,
@DateTo Date,
@TranFrom Char(10),
@TranTo Char(10),
@Post Char(1)
|
By Edhy Rijo - 8/10/2012
Hi Gerard,
What is the code that is triggering the exception? posting the exception will help in finding out where exactly the code is failing.
Also in your Client Code, pass the x.Date to the @DateFrom
DateTime x = new DateTime(this.dateFrom.CurrentYear, this.dateFrom.CurrentMonth, this.dateFrom.CurrentDay);
comm.Parameters.AddWithValue("@DateFrom", x.Date).SqlDbType= SqlDbType.Date;
|
By Ger Cannoll - 8/10/2012
Hi Edhy.
Did as you suggested (comm.Parameters.AddWithValue("@DateFrom", x.Date).SqlDbType= SqlDbType.Date;..... still an error
I have attached the error in case i gives a clue.
Regards,
Gerard
|
By Ger Cannoll - 8/10/2012
Sorry previos post does not seem to have attachment....attachemnt now
|
By Edhy Rijo - 8/10/2012
Put a break in your code and do a screenshot of the code in the debugger where it breaks, also please copy and paste the code here. It looks like your date or the value you are sending is a string and not a date.
|
By Ger Cannoll - 8/10/2012
Hi Edhy.... do you mean put a break in the Stored Procedure Code (Standard TSQL) where the error is happening ....didnt know you could do that ??
|
By Edhy Rijo - 8/10/2012
Ger Cannoll (8/10/2012)
Hi Edhy.... do you mean put a break in the Stored Procedure Code (Standard TSQL) where the error is happening ....didnt know you could do that ??
Nope, if the error is coming from the Stored Procedure, please paste the SP here or if it is too long, then copy it to a text document and attach it here.
Also did you try running the sp in MSMS (Microsoft SQL Server Management Studio)? if so, did it give you an error there too?
|
By Ger Cannoll - 8/10/2012
Hi Edhy. Main bits of my SProc is:
SELECT * from ITR
WHERE
((@StageOperator = '=' AND ITR_STAGE = @StageQuery ) or (@StageOperator = '>=' and ITR_STAGE >= @StageQuery ) )
AND (ITR_POST = @PostQuery)
AND (ITR_CUSREF >= @AccountFrom OR Len(LTRIM(RTRIM(@AccountFrom))) = 0)
AND (ITR_CUSREF <= @AccountTo OR Len(LTRIM(RTRIM(@AccountTo))) = 0)
AND (@TranQuery >= @TranFrom OR Len(Ltrim(Rtrim(@TranFrom))) = 0)
AND (@TranQuery <= @TranTo OR Len(Ltrim(Rtrim(@TranTo))) = 0)
AND (@DateQuery >= @DateFrom OR @DateFrom = '1800-01-01' )
AND (@DateQuery <= @DateTo OR @DateTo = '1800-01-01')
OPTION (RECOMPILE)
Suspect the fact that have 'Datefrom = '1800-01-01' is causing it to be treated as a string, and I dont get error if I ensure date is this format when called from SSMS
|
By Edhy Rijo - 8/10/2012
Hi Gerard,
I guess you want to be able to pass a date to filter the data, but if the date is 1800-1-1, then to ignore that condition. If that is the case, then try casting the '1800-01-01' to a date value as shown below and that should take care of the error.
AND (@DateQuery >= @DateFrom OR @DateFrom = cast('1800-01-01' as date)
AND @DateQuery <= @DateTo OR @DateTo = cast('1800-01-01' as date))
Also, pay attention to the open and close parenthesis, since you are trying to filter by a date condition, the whole expression should be enclosed in a parenthesis, so it will be a single condition.
|
By Ger Cannoll - 8/10/2012
Hi Edhy. I have established what the problem here is , but dont know how to fix it.
In my Select Command, depending on a parameter passed into the stored procedure, i need to change the Field from the Table that is used. Sometimes it may be the Order date, sometimes the Despatch date and sometimes the Invoice date. I pass in a parmater and then have a series of Ifs to store the filed name into a variable. Then, because I am using the variable in the Select string, and not the actual Field name, this gives the error. Here is an example of a cut doen SP which will give an error:
ALTER PROCEDURE [dbo].[TestSPR]
@Paramdate Date
AS
BEGIN
SET NOCOUNT ON;
--SELECT * FROM ITR WHERE ITR_ORDDAT >= @Paramdate (This command works Fine)
DECLARE @Mydate VARCHAR
set @MyDate = 'ITR_ORDDAT'
SELECT * FROM ITR WHERE @MyDate >= @Paramdate // This Line gives an error
END
So if I use SELECT * FROM ITR WHERE ITR_ORDDAT >= @Paramdate ...... works fine
If I store 'ITR_ORDDAT' into @MyDate and run the select (as above) , gives an conversion string to date/Time error.
|
By Edhy Rijo - 8/11/2012
Hi Gerard,
Ger Cannoll (8/11/2012)
Hi Edhy. I have established what the problem here is , but dont know how to fix it.
In my Select Command, depending on a parameter passed into the stored procedure, i need to change the Field from the Table that is used. Sometimes it may be the Order date, sometimes the Despatch date and sometimes the Invoice date.
Now I understand  if you would have posted the whole stored procedure before, we would have figure this one out faster 
You have to use a CASE statement in your sp WHERE condition to manage this. In the sample code below, you have to populate the parameter @MyDateFieldName with the expected value used in the CASE WHEN of the sp. All this is nice, but be aware that you should test all possible conditions and make sure your table ITR has the recommended indexes to optimize these WHERE conditions.
Have fun!!! 
ALTER PROCEDURE [dbo].[TestSPR] @Paramdate Date, @MyDateFieldName VARCHAR AS BEGIN SET NOCOUNT ON; SELECT * FROM ITR WHERE CASE @MyDateFieldName WHEN 'OrderDate' THEN ITR_ORDDAT WHEN 'InvoiceDate' THEN ITR_INVDAT END >= @ParamDate END
|
By Ger Cannoll - 8/12/2012
Hi Edhy.
I have this working now. Thanks for all your help
I intially had though that Parameter Substitiution worked as I had also some Paranater Substitiution on for Character based Fields (as in @MyCharFieldName >= 'SomeString')
I 'thought' that this was workong as no error was beong generated, but now realise all that was happenong was that the Charcater String --> MyCahrFiledName was being compared to the sting ---> SomeString. I was locked in a VFP timewarp and assmed that SQL SProcs worked in a similar way to VFP Macro Substitution.
Regards,
Gerard
|
By Edhy Rijo - 8/13/2012
Hi Gerard,
I am glad we clear this one out.
Ger Cannoll (8/13/2012)
I was locked in a VFP timewarp and assmed that SQL SProcs worked in a similar way to VFP Macro Substitution.
Yeah, I loved VFP Macro Substitution, and it has been hard working without it in some situations but MS-SQL is a totally different animal than VFP when dealing with data, and the things that can be done with TSQL and SQL Engine are just amazing when compared with the way we did it back then with VFP.
|