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.
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
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!
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....
INSERT INTO request (requesttitle, date_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 requesttitle, datepart(mm, date_closed)as month
Where datepart(mm, date_closed) = 3
and date_closed IS NOT NULL
DROP TABLE request
(8 row(s) affected)
Quaterly billing 3
Amend of Information 3