StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » How to extract date Prtion from SF DateBox

How to extract date Prtion from SF DateBox

Ger Cannoll — Mon, 13 Aug 2012 06:56:50 GMT

How do I extract the Date Only from a SF DateBox

e.g. DateTime myDate = DateTime.Today.Date gets Date Only Part

But I cant seem to do: DateTime myDatepart = MySfDateBox.Date

RE: How to extract date Prtion from SF DateBox

Edhy Rijo — Mon, 13 Aug 2012 06:56:50 GMT

Hi Gerard,

I am glad we clear this one out. :)

[quote][b]Ger Cannoll (8/13/2012)[/b][hr]I was locked in a VFP timewarp and assmed that SQL SProcs worked in a similar way to VFP Macro Substitution.[/quote]
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.

RE: How to extract date Prtion from SF DateBox

Ger Cannoll — Mon, 13 Aug 2012 04:46:27 GMT

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

RE: How to extract date Prtion from SF DateBox

Edhy Rijo — Sat, 11 Aug 2012 09:44:15 GMT

Hi Gerard,

[quote][b]Ger Cannoll (8/11/2012)[/b][hr]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.[/quote]

Now I understand :) if you would have posted the whole stored procedure before, we would have figure this one out faster :D

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!!! :hehe:

[codesnippet]

ALTER PROCEDURE [dbo].[TestSPR]

@Paramdate Date,

@MyDateFieldName VARCHAR

BEGIN

SET NOCOUNT ON;

SELECT

FROM

ITR

WHERE

CASE @MyDateFieldName

WHEN 'OrderDate' THEN ITR_ORDDAT

WHEN 'InvoiceDate' THEN ITR_INVDAT

END >= @ParamDate

END

[/codesnippet]

RE: How to extract date Prtion from SF DateBox

Ger Cannoll — Sat, 11 Aug 2012 05:51:07 GMT

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.

RE: How to extract date Prtion from SF DateBox

Edhy Rijo — Fri, 10 Aug 2012 12:01:19 GMT

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.

[codesnippet]
AND (@DateQuery >= @DateFrom OR @DateFrom = cast('1800-01-01' as date)
AND @DateQuery <= @DateTo OR @DateTo = cast('1800-01-01' as date))
[/codesnippet]

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.

RE: How to extract date Prtion from SF DateBox

Ger Cannoll — Fri, 10 Aug 2012 11:38:13 GMT

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

RE: How to extract date Prtion from SF DateBox

Edhy Rijo — Fri, 10 Aug 2012 11:33:09 GMT

[quote][b]Ger Cannoll (8/10/2012)[/b][hr]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 ??[/quote]
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?

RE: How to extract date Prtion from SF DateBox

Ger Cannoll — Fri, 10 Aug 2012 11:15:07 GMT

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 ??

RE: How to extract date Prtion from SF DateBox

Edhy Rijo — Fri, 10 Aug 2012 10:56:01 GMT

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.

RE: How to extract date Prtion from SF DateBox

Ger Cannoll — Fri, 10 Aug 2012 10:20:13 GMT

Sorry previos post does not seem to have attachment....attachemnt now

RE: How to extract date Prtion from SF DateBox

Ger Cannoll — Fri, 10 Aug 2012 10:17:36 GMT

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

RE: How to extract date Prtion from SF DateBox

Edhy Rijo — Fri, 10 Aug 2012 09:14:22 GMT

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;

RE: How to extract date Prtion from SF DateBox

Ger Cannoll — Fri, 10 Aug 2012 06:31:47 GMT

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)

RE: How to extract date Prtion from SF DateBox

Edhy Rijo — Thu, 09 Aug 2012 15:37:40 GMT

Hi Gerard,

In my Stored Procedures I cast the datetime as follow:
[codesnippet]
@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
[/codesnippet]

And in SF, I use an SQLCommand object and pass the date as a parameter using bo.DateFieldName.Date
[codesnippet]

            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

[/codesnippet]

RE: How to extract date Prtion from SF DateBox

Ger Cannoll — Thu, 09 Aug 2012 10:16:50 GMT

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 ?

RE: How to extract date Prtion from SF DateBox

Ivan George Borges — Thu, 02 Aug 2012 04:37:51 GMT

You're welcome, Ger. :cool:

RE: How to extract date Prtion from SF DateBox

Ger Cannoll — Thu, 02 Aug 2012 03:14:13 GMT

Thanks Ivan. Thars worked fine.

regards,

Gerard

RE: How to extract date Prtion from SF DateBox

Ivan George Borges — Wed, 01 Aug 2012 13:11:52 GMT

Hi Ger.

Try

[codesnippet]DateTime myDatepart = new DateTime(MySfDateBox.CurrentYear, MySfDateBox.CurrentMonth, MySfDateBox.CurrentDay);[/codesnippet]