Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: Return Null Results

    Hi,
    I have this problem with MS SQL Server.

    I have this table 'Request' in which there's 2 fields 'RequestName' and 'DateSubmitted'.
    The datatype for 'DateSubmitted' is datetimn.

    I have to write a query to extract all the 'RequestName' that falls under each month based on the 'DateSubmitted'.

    For example, the table 'Request' has 12 rows, each row ('DateSubmitted') with the month of the year. Like row1 = January, row2 = February etc.

    For each month, I have to extract all the 'RequestName' for that month based on 'DateSubmitted' field.
    My query is like this:

    select RequestName, datename(month, DateSubmitted)as month
    from Request where datename(month, DateSubmitted) = 'March'.

    By right it should return just one row right?

    Instead, it returned me all 12 rows, only one row with value 'March' and the other 11 rows with value 'NULL'. Even if the other 11 rows have value of other months, when i run the above query, it will return me null.

    Any help is appreciated.
    thanks a million.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Return Null Results

    Personally I would use the month number instead of the month name, as you have it written now it is case-sensitive. If SQL returns 'MARCH' it will not match with your 'March'. Also add a criteria not to return nulls (even though it shouldn't in the first place). Here is how I would write your query:

    SELECT RequestName, datename(month, DateSubmitted)as month
    FROM Request WHERE Month(DateSubmitted) = 3 AND DateSubmitted IS NOT NULL
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    6

    Smile

    Hi,
    Thanks for your reply.
    This is how I re-write my query:

    select RequestName, datepart(mm, DateSubmitted)as month
    from Request
    and datepart(mm, DateSubmitted) = 3
    and DateSubmittedIS NOT NULL

    but the result is still 10 rows, 9 of which is null...
    Is there something wrong with the date format?

    Thanks alot!

  4. #4
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    select n1, datepart(mm, d1)as month
    from test1
    Where datepart(mm, d1) = 3
    and d1 IS NOT NULL


    Returns 1 row for me. I presume you pasted query was a typo?

    Rgds,
    Rob.

  5. #5
    Join Date
    Mar 2004
    Posts
    6
    Hi,
    hmm i've tried but it still return me 10 rows with 9 of which is null..
    Is there any possibility that the date format is wrong or what?
    I cannot think of any reason why it always return me null rows....

    Thanks alot!

  6. #6
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    Post the design of your table, the actual query you are running, and the data within the table, and I'll have a look.

    Rgds,
    Jim

  7. #7
    Join Date
    Mar 2004
    Posts
    6
    Hi~
    I've PM you..
    Thanks alot!

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by reneling
    Hi~
    I've PM you..
    Thanks alot!
    If you post the information in a regular message, more of us could look at it. Maybe one of us might see a detail that the others missed, or reply sooner (we sleep in shifts)!

    -PatP

  9. #9
    Join Date
    Mar 2004
    Posts
    6
    Hi,
    Below is the query I've used.

    select requesttitle, datepart(mm, date_closed)as month
    from request
    Where datepart(mm, date_closed) = 3
    and date_closed IS NOT NULL

    This is the actual data if I just select * from request

    requesttitle date_closed
    ----------------------------- -----------------------
    Gateway Connection 2004-02-05 00:00:00:00
    Paging 2004-02-05 00:00:00:00
    Update FR pricing for new rout 2003-10-10 00:00:00:00
    New Routes
    Change of information 2003-10-10 00:00:00:00
    Discount 2004-03-08 00:00:00:00
    Quaterly billing 2004-03-12 00:00:00:00
    Amend of Information 2004-03-08 00:00:00:00


    This is what is return when I run my query:

    select requesttitle, datepart(mm, date_closed)as month
    from request
    Where datepart(mm, date_closed) = 3
    and date_closed IS NOT NULL

    requesttitle month
    ------------------------------ -----------
    Gateway Connection NULL
    Paging NULL
    Update FR pricing for new rout NULL
    New Routes NULL
    Change of Informaton NULL
    Discount 3
    Quaterly billing 3
    Amend of Information 3


    Thanks alot for the help~
    Last edited by reneling; 03-25-04 at 04:10.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When I run:
    PHP Code:
    CREATE TABLE request (
       
    requesttitle        VARCHAR(40)    NULL
    ,  date_closed        DATETIME    NULL
       
    )

    INSERT INTO request (requesttitledate_closed)
       
    SELECT 'Gateway Connection''2004-02-05'
       
    UNION ALL SELECT 'Paging''2004-02-05'
       
    UNION ALL SELECT 'Update FR pricing for new rout''2003-10-10'
       
    UNION ALL SELECT 'New Routes'NULL 
       UNION ALL SELECT 
    'Change of information''2003-10-10'
       
    UNION ALL SELECT 'Discount''2004-03-08'
       
    UNION ALL SELECT 'Quaterly billing''2004-03-12'
       
    UNION ALL SELECT 'Amend of Information''2004-03-08'

    select requesttitledatepart(mmdate_closed)as month 
    from request
    Where datepart
    (mmdate_closed) = 3
    and date_closed IS NOT NULL

    DROP TABLE request 
    I get:
    PHP Code:
    (8 row(saffected)

    requesttitle                             month       
    ---------------------------------------- ----------- 
    Discount                                 3
    Quaterly billing                         3
    Amend of Information                     3

    (3 row(saffected
    -PatP

  11. #11
    Join Date
    Mar 2004
    Posts
    6
    Thanks lot for your help!
    I think its the table view that got problem.
    Solving it now.
    Thanks alot~

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •