Using Month and year only to get all data

[hsteo]

I would like to find out if extraction of data from database is possible by using the mm/yyyy only.

My code is as below but it is not a workable code:


SELECT vessel, voyage
FROM dneirout
WHERE CONVERT(DATETIME, dneirout.CONTAINER_OUT_DATE, 103) >= CONVERT(DATETIME, '09/2008', 103)

[hspc]

The 3rd paramter to CONVERT is used only when you convert to a character type, so it won't work in your case.
Instead, you should use DATEDIFF function like this:
SELECT vessel, voyage
FROM dneirout
WHERE DATEDIFF(month,'20080901',dneirout.CONTAINER_OUT_DATE)>=0

Check the DATEDIFF function specs in BOL for more details.

[Shuja Ali]

What database are you using?

If it is SQL Server then look at DatePart function. This function will let you divide the date into Month, Day and Year.

[hsteo]

Hi, i am using Microsoft SQL Server Management Studio Express,

Maybe I try defining my problem here, I would like to do a monthly consolidation of records if the user intend to look at .for example September 2007, so i will extract all the data from 1/09/2007 to 31/09/2007.

HTH

[davide++]

Hi all

A part from September constains 30 days, no 31, you should use DatePart function, as Shuja Ali said.
So a query that solves your problem may be

SELECT vessel, voyage
FROM dneirout
WHERE DatePart('m', CONTAINER_OUT_DATE) = 9
AND DatePart('yyyy', CONTAINER_OUT_DATE) = 2007

[hspc]

Hi, i am using Microsoft SQL Server Management Studio Express,

Maybe I try defining my problem here, I would like to do a monthly consolidation of records if the user intend to look at .for example September 2007, so i will extract all the data from 1/09/2007 to 31/09/2007.
HTH

This will extract records in sept.:
SELECT vessel, voyage
FROM dneirout
WHERE
DATEDIFF(month,CONTAINER_OUT_DATE,'20070901')=0

Also the solution that davide++ mentioned will work.