none
Date Parameter not working

    Question

  • I have a weird problem. Let me try to set it up for you.

    I have one SQL table(Table1) with all the data, I have another SQL table(Parameters) with a few date fields.

    My query

    SELECT A, B,C, ContractDate
    FROM Table1
    WHERE ContractDate>='2012-01-01'

    Works great. BUT when I add in a parameter like this, it accepts it like it is a correct query but acts like the WHERE clause just doesn't exist.

    DECLARE @STARTDate as date
    SET @STARTDate = '2012-01-01'
    select @STARTDate = value from Parameters where Name ='STARTDate';
    
    SELECT A, B,C, ContractDate
    FROM Table1
    WHERE ContractDate>=@STARTDate

    How come this doesn't work?


    • Edited by DCDeez Tuesday, October 15, 2013 7:04 PM
    Tuesday, October 15, 2013 2:55 PM

Answers

  • First of all, we want to see what this @StartDate parameter is after the select statement.

    So, try this:

    DECLARE @STARTDate as date
    SET @STARTDate = '20120101'
    select @STARTDate = value from Parameters where Name ='STARTDate';
    
    SELECT @StartDate  as StartDate -- what is the value now?
    
    SELECT A, B,C, ContractDate
    FROM Table1
    WHERE ContractDate>=@STARTDate


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by DCDeez Tuesday, October 15, 2013 3:39 PM
    Tuesday, October 15, 2013 3:18 PM
    Moderator
  • "STARTDate" seems to be a hard coded parameter somewhere.

    StartDate just returns "2008-01-01" no matter what you set it to.

    I changed the parameter to "SDate" and it worked fine

    • Marked as answer by DCDeez Tuesday, October 15, 2013 4:58 PM
    Tuesday, October 15, 2013 4:58 PM

All replies

  • Please post the DDL of Tables "Table1" and "Parameter".

    What is the Column "ContractDate" data type? If the data type is DATETIME what's its values  format?


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012


    Tuesday, October 15, 2013 3:02 PM
  • Table1

    A   VARCHAR(9)

    B   VARCHAR(10)

    C   VARCHAR(2)

    ContractDate   Date

    Parameter

    Name   NVARCHAR(50)

    Value   NVARCHAR(50)

    Tuesday, October 15, 2013 3:12 PM
  • First of all, we want to see what this @StartDate parameter is after the select statement.

    So, try this:

    DECLARE @STARTDate as date
    SET @STARTDate = '20120101'
    select @STARTDate = value from Parameters where Name ='STARTDate';
    
    SELECT @StartDate  as StartDate -- what is the value now?
    
    SELECT A, B,C, ContractDate
    FROM Table1
    WHERE ContractDate>=@STARTDate


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by DCDeez Tuesday, October 15, 2013 3:39 PM
    Tuesday, October 15, 2013 3:18 PM
    Moderator
  • "STARTDate" seems to be a hard coded parameter somewhere.

    StartDate just returns "2008-01-01" no matter what you set it to.

    I changed the parameter to "SDate" and it worked fine

    • Marked as answer by DCDeez Tuesday, October 15, 2013 4:58 PM
    Tuesday, October 15, 2013 4:58 PM
  • Why are you exempt from the requirement to post DDL and sample data? 

    Your narrative is wrong. Columns are not anything like fields. The only display format allowed in ANSI/ISO Standard SQL is ISO-8601, which means  "yyyy-mm-dd"; no ISO temporal standard uses commas!! 

    "Parameters" is a meta-date term and cannot be a table name in a ISO-11179 conformant schema. 

    Try again, after you have learned a few basics and some Netiquette, please. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, October 15, 2013 6:23 PM
  • CELKO,

    DDL was posted, I'll post a sample of the data next time but the sample data wouldn't have helped Naomi with his answer either way this time.

    My narrative is wrong? Where do I mention columns or fields in my question or my explanation?

    Yes, the dates were copied over wrong when I was typing this on my desktop while looking at my laptop. You are correct on that one thing. I'll correct my original post. Dates within my query are actually ...

    SET @STARTDate = '2013-01-01'

    "Parameters" may not be best practice to name a table that way but it is accepted and has had no problems in any queries thus far. Where would this become an issue?

    Naomi had no problem directing me in the right direction with what I've posted. Sorry for inconveniencing you. I'll include sample data in the future.

    Tuesday, October 15, 2013 7:03 PM
  • >> DDL was posted, I'll post a sample of the data next time but the sample data wouldn't have helped Naomi with his answer either way this time. <<

    Why is not knowing at types, keys and constraints a good thing?? 

    >> Where do I mention columns or fields in my question or my explanation? <<

    First paragraph: I have one SQL table(Table1) with all the data, I have another SQL table(Parameters) with a few date fields.

    The term "field" is the year, month, day, hour, minute,  second parts of a TIMESTAMP. 

    Yes, the dates were copied over wrong when I was typing this on my desktop while looking at my laptop. You are correct on that one thing. I'll correct my original post. Dates within my query are actually ...

    SET @STARTDate = '2013-01-01'

    >> "Parameters" may not be best practice to name a table that way but it is accepted and has had no problems in any queries thus far. Where would this become an issue? <<

    When someone else has to maintain the code. When you have to face an ISO-11179 audit. When you want to be regarded as a professional and not a noob or a hack.



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, October 15, 2013 7:47 PM
  • CELKO,

    1. Types were posted. There are no constraints or keys for this table.

    2. Touche, I didn't notice that slip with the fields vs column...

    3. I personally have no control over the naming convention of the tables. But I'll keep that in mind for anything I can change in the future.

    Also I am a complete noob, so thanks for getting back to me.

    Thanks!

    Tuesday, October 15, 2013 7:59 PM