Post reply

Validate Date

  • July 10, 2008 at 12:46 pm

    #103528

    I have a parent table ProductType and child tablE Product. ProductType has one to many relationship with Product table.

    Whenever I update the StartDate of the ProductType, I shud always verify whether the

    1. StartDate is always greater than or equal to today's date

    2. StartDate is lesser than or equal to the start dates of its products in Product table.

    If the above is valid then i return true else false.

    Here is the code.

    ALTER PROCEDURE [dbo].[ProductType_ValidateStartDate]

    (

    @ProductTypeID INT,

    @StartDate smalldatetime

    )

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @temp TABLE

    (

    tempBit int

    )

    Insert into @temp(tempBit) SELECT CASE WHEN (ISDATE(@StartDate) = 1) AND (@StartDate = dbo.DateOnly(GETDATE())

    THEN '1' ELSE '0' END

    FROM Product, ProductType

    WHERE ProductType.ProductTypeID = @ProductTypeID AND

    ProductType.ProductTypeID = Product.ProductTypeID

    If (SELECT MIN(T.tempBit) FROM @temp T) = 0

    BEGIN

    SELECT '0' AS ValidStartDate

    END

    ELSE

    SELECT '1' AS ValidStartDate

    END

    Is there any better way to do this ?

    Any suggestions are greatly appreciated.

    Thanks in advance

  • July 10, 2008 at 9:20 pm

    #841215

    I think this is simpler and more efficient:

    ALTER PROCEDURE [dbo].[ProductType_ValidateStartDate](

    @ProductTypeID INT,

    @StartDate smalldatetime)

    AS

    BEGIN

    SET NOCOUNT ON

    Declare @Today smalldatetime

    -- beginning of today:

    Select @Today = Cast(Cast(getdate() as int) as smalldatetime)

    IF @StartDate < @Today-- Start date cannot be before today

    Begin

    Select 0 as ValidStartDate

    End

    ELSE

    Begin

    --start date cannot be after its products start dates

    Select Case When @StartDate > Min(StartDate) Then 0

    Else 1 End as ValidStartDate

    From Product

    Join ProductType ON ProductType.ProductTypeID = @ProductTypeID

    And ProductType.ProductTypeID = Product.ProductTypeID

    End

    END

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 10, 2008 at 9:31 pm

    #841219

    RBarryYoung,

    Wow..This is really simple.. Thanks a lot... I really appreciate your help.

    Thanks for your time,

    Kayal

  • July 11, 2008 at 5:43 am

    #841373

    Thanks for the feedback.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 11, 2008 at 10:04 am

    #841654

    RBarryYoung,

    I have a Question,

    Is 1 better or 2 better(Join).

    1.

    FROM Product, ProductType

    WHERE ProductType.ProductTypeID = @ProductTypeID AND

    ProductType.ProductTypeID = Product.ProductTypeID

    OR

    2.

    From Product

    Inner Join ProductType ON ProductType.ProductTypeID = @ProductTypeID

    And ProductType.ProductTypeID = Product.ProductTypeID

    I have a select from 8 tables .. Is it better to use inner join between those 8 tables ON the key or just have the list of tables in the FROM clause and Where all the conditions like key from one table = key from other table ? When I see the execution plan, it shows joins are more costly than the other, though not much of a difference. Which do you think is better performance wise. Is there really any difference?

    Thanks again for ur suggetions. This is really helping budding programmers like me. I appreciate your help.

    Thanks,

    Kayal

  • July 11, 2008 at 10:17 am

    #841675

    mailsar (7/11/2008)

    RBarryYoung,

    I have a Question,

    Is 1 better or 2 better(Join).

    1.

    FROM Product, ProductType

    WHERE ProductType.ProductTypeID = @ProductTypeID AND

    ProductType.ProductTypeID = Product.ProductTypeID

    OR

    2.

    From Product

    Inner Join ProductType ON ProductType.ProductTypeID = @ProductTypeID

    And ProductType.ProductTypeID = Product.ProductTypeID

    I have a select from 8 tables .. Is it better to use inner join between those 8 tables ON the key or just have the list of tables in the FROM clause and Where all the conditions like key from one table = key from other table ? When I see the execution plan, it shows joins are more costly than the other, though not much of a difference. Which do you think is better performance wise. Is there really any difference?

    Thanks again for ur suggetions. This is really helping budding programmers like me. I appreciate your help.

    Thanks,

    Kayal

    Method 2 is the ANSI standard, and the way I prefer to do joins. Seperating the JOINS in the FROM clause from the filter criteria in the WHERE clause also makes the queries easier to read and understand.

    😎

  • July 11, 2008 at 11:27 am

    #841732

    I agree with Lynn.

    Using commas is the older, "SQL-89" syntax and it is deprecated now.

    Using "JOIN" is the newer "SQL-92" syntax and it IMHO much preferred because:

    1) It is much easier for people to read, esp. when learning SQL. and..

    2) It has more options: it is possible to combine both inner and outer type

    test conditions in the same join. this was never possible with the old syntax. and..

    3) You can no longer do the outer joins with the old syntax.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 11, 2008 at 12:13 pm

    #841749

    Thanks BarryYoung and Lynn. It makes sense why people prefer joins as its simpler, preferred and has more features. But when it comes to performance, I guess not much of a difference.

    Thank You. I appreciate it 🙂

  • July 11, 2008 at 12:53 pm

    #841766

    Beware of rounding!

    If you run the suggested code PM, you will not get today's beginning.

    Declare @Today smalldatetime

    -- beginning of today:

    Select @Today = Cast(Cast(getdate() as int) as smalldatetime)

    Try this.

    Declare @Today smalldatetime

    -- beginning of today:

    Set @Today = datediff(day, '19000101', getdate())

    N 56°04'39.16"
    E 12°55'05.25"

  • July 11, 2008 at 1:49 pm

    #841789

    Thanks Peso for the info.

    I always use this to get the date part

    @Today = dateadd(dd,0, datediff(dd,0,getdate()))

    Thanks,

    Kayal

  • July 11, 2008 at 3:20 pm

    #841862

    Peso (7/11/2008)

    Beware of rounding!

    If you run the suggested code PM, you will not get today's beginning.

    Declare @Today smalldatetime

    -- beginning of today:

    Select @Today = Cast(Cast(getdate() as int) as smalldatetime)

    Try this.

    Declare @Today smalldatetime

    -- beginning of today:

    Set @Today = datediff(day, '19000101', getdate())

    Oh, rats! You're right Peso, I forgot that CAST rounds instead of truncates. You can use this instead:

    Select Cast(Floor(Cast(getdate() as float)) as smalldatetime)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 11, 2008 at 4:52 pm

    #841893

    rbarryyoung (7/11/2008)

    this was never possible with the old syntax

    That's not true...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 11, 2008 at 8:36 pm

    #841921

    How's that?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 11, 2008 at 8:50 pm

    #841923

    Jeff Moden (7/11/2008)

    rbarryyoung (7/11/2008)

    this was never possible with the old syntax

    That's not true...

    Hey, I just realized... This is the post that you broke 10,000 on! :w00t:

    Woo, hooo! Makes becoming an MVP pale by comparision... hey where is everyone?...

    Oh yeah, its Friday night. oh well,...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 12, 2008 at 9:15 am

    #841990

    rbarryyoung (7/11/2008)

    Jeff Moden (7/11/2008)

    rbarryyoung (7/11/2008)

    this was never possible with the old syntax

    That's not true...

    rbarryyoung (7/11/2008)

    How's that?

    rbarryyoung (7/11/2008)

    I agree with Lynn.

    Using commas is the older, "SQL-89" syntax and it is deprecated now.

    Using "JOIN" is the newer "SQL-92" syntax and it IMHO much preferred because:

    1) It is much easier for people to read, esp. when learning SQL. and..

    2) It has more options: it is possible to combine both inner and outer type

    test conditions in the same join. this was never possible with the old syntax. and..

    3) You can no longer do the outer joins with the old syntax.

    I probably should have asked by what you meant, but up there in item 2, Barry... you said that combining inner and outer type test conditions where not possible in the same join. If you mean what I think you mean, then that's not true because something like the following used to work just fine...

    SELECT yada-yada

    FROM sometable1, sometable2, sometable3

    WHERE sometable1.somecol *= sometable2.somecol --Left Outer Join

    AND sometable1.somecol = sometable3.somecol --Inner Join

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply