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

 

 

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.

Edhy Rijo

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.

Hi Gerard,



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

Edhy Rijo

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

Hi Gerard,

I am glad we clear this one out.


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.

Edhy Rijo