Problem with date

Hi,

I have the foolowing view for grouping info by Quarter. I got some null
value in the Qtr column when the ME_Date is
31/3/2008 11:46:18 AM and 30/6/2007 7:04:05 PM. It appears that these dates
have not been converted to Qtr. Am I missing something?

SELECT TOP (100) PERCENT Me_MeetingID AS [Number Of Meeting], CASE WHEN
me_date BETWEEN '20080101' AND
'20080331' THEN '1st Qtr 08' WHEN me_date BETWEEN
'20080401' AND '20080630' THEN '2nd Qtr 08' WHEN me_date BETWEEN '20080601'
AND
'20080930' THEN '3rd Qtr 08' WHEN me_date BETWEEN
'20081001' AND '20081231' THEN '4th Qtr 08' WHEN me_date BETWEEN '20070101'
AND
'20070331' THEN '1st Qtr 07' WHEN me_date BETWEEN
'20070401' AND '20070630' THEN '2nd Qtr 07' WHEN me_date BETWEEN '20070701'
AND
'20070930' THEN '3rd Qtr 07' WHEN me_date BETWEEN
'20071001' AND '20071231' THEN '4th Qtr 07' END AS Qtr, Me_Date
FROM dbo.tblMeetings
ORDER BY Qtr


Could someone advice?

SF

Generally, don't use BETWEEN for dates. Instead do

SELECT Me_MeetingID AS [Number Of Meeting],
CASE WHEN me_date >= '20080101' AND me_date < '20080401' THEN '1st Qtr 08'
WHEN me_date >= '20080401' AND me_date < '20080701' THEN '2nd Qtr 08'
WHEN me_date >= '20080701' AND me_date < '20081001' THEN '2nd Qtr 08'
WHEN me_date >= '20081001' AND me_date < '20090101' THEN '2nd Qtr 08'
END AS Qtr,
Me_Date
FROM dbo.tblMeetings

etc

Also, the TOP (100) PERCENT is meaningless and there is no reason for it to
be there.

Tom

"SF" wrote in message
news:e3qYjpMCJHA.1816-at-TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I have the foolowing view for grouping info by Quarter. I got some null
> value in the Qtr column when the ME_Date is
> 31/3/2008 11:46:18 AM and 30/6/2007 7:04:05 PM. It appears that these
> dates have not been converted to Qtr. Am I missing something?
>
> SELECT TOP (100) PERCENT Me_MeetingID AS [Number Of Meeting], CASE
> WHEN me_date BETWEEN '20080101' AND
> '20080331' THEN '1st Qtr 08' WHEN me_date BETWEEN
> '20080401' AND '20080630' THEN '2nd Qtr 08' WHEN me_date BETWEEN
> '20080601' AND
> '20080930' THEN '3rd Qtr 08' WHEN me_date BETWEEN
> '20081001' AND '20081231' THEN '4th Qtr 08' WHEN me_date BETWEEN
> '20070101' AND
> '20070331' THEN '1st Qtr 07' WHEN me_date BETWEEN
> '20070401' AND '20070630' THEN '2nd Qtr 07' WHEN me_date BETWEEN
> '20070701' AND
> '20070930' THEN '3rd Qtr 07' WHEN me_date BETWEEN
> '20071001' AND '20071231' THEN '4th Qtr 07' END AS Qtr, Me_Date
> FROM dbo.tblMeetings
> ORDER BY Qtr
>
>
> Could someone advice?
>
> SF
>

Sorry, of course the last two WHEN's should be

WHEN me_date >= '20080701' AND me_date < '20081001' THEN '3rd Qtr 08'
WHEN me_date >= '20081001' AND me_date < '20090101' THEN '4th Qtr 08'

Tom

"Tom Cooper" wrote in message
news:O$xLL1MCJHA.5012-at-TK2MSFTNGP05.phx.gbl...
> Generally, don't use BETWEEN for dates. Instead do
>
> SELECT Me_MeetingID AS [Number Of Meeting],
> CASE WHEN me_date >= '20080101' AND me_date < '20080401' THEN '1st Qtr 08'
> WHEN me_date >= '20080401' AND me_date < '20080701' THEN '2nd Qtr 08'
> WHEN me_date >= '20080701' AND me_date < '20081001' THEN '2nd Qtr 08'
> WHEN me_date >= '20081001' AND me_date < '20090101' THEN '2nd Qtr 08'
> END AS Qtr,
> Me_Date
> FROM dbo.tblMeetings
>
> etc
>
> Also, the TOP (100) PERCENT is meaningless and there is no reason for it
> to be there.
>
> Tom
>
> "SF" wrote in message
> news:e3qYjpMCJHA.1816-at-TK2MSFTNGP06.phx.gbl...
>> Hi,
>>
>> I have the foolowing view for grouping info by Quarter. I got some null
>> value in the Qtr column when the ME_Date is
>> 31/3/2008 11:46:18 AM and 30/6/2007 7:04:05 PM. It appears that these
>> dates have not been converted to Qtr. Am I missing something?
>>
>> SELECT TOP (100) PERCENT Me_MeetingID AS [Number Of Meeting], CASE
>> WHEN me_date BETWEEN '20080101' AND
>> '20080331' THEN '1st Qtr 08' WHEN me_date BETWEEN
>> '20080401' AND '20080630' THEN '2nd Qtr 08' WHEN me_date BETWEEN
>> '20080601' AND
>> '20080930' THEN '3rd Qtr 08' WHEN me_date BETWEEN
>> '20081001' AND '20081231' THEN '4th Qtr 08' WHEN me_date BETWEEN
>> '20070101' AND
>> '20070331' THEN '1st Qtr 07' WHEN me_date BETWEEN
>> '20070401' AND '20070630' THEN '2nd Qtr 07' WHEN me_date BETWEEN
>> '20070701' AND
>> '20070930' THEN '3rd Qtr 07' WHEN me_date BETWEEN
>> '20071001' AND '20071231' THEN '4th Qtr 07' END AS Qtr, Me_Date
>> FROM dbo.tblMeetings
>> ORDER BY Qtr
>>
>>
>> Could someone advice?
>>
>> SF
>>
>
>

Great. It works.

Thank Tom.

SF

"Tom Cooper" wrote in message
news:eEulXCNCJHA.4884-at-TK2MSFTNGP02.phx.gbl...
> Sorry, of course the last two WHEN's should be
>
> WHEN me_date >= '20080701' AND me_date < '20081001' THEN '3rd Qtr 08'
> WHEN me_date >= '20081001' AND me_date < '20090101' THEN '4th Qtr 08'
>
> Tom
>
> "Tom Cooper" wrote in message
> news:O$xLL1MCJHA.5012-at-TK2MSFTNGP05.phx.gbl...
>> Generally, don't use BETWEEN for dates. Instead do
>>
>> SELECT Me_MeetingID AS [Number Of Meeting],
>> CASE WHEN me_date >= '20080101' AND me_date < '20080401' THEN '1st Qtr
>> 08'
>> WHEN me_date >= '20080401' AND me_date < '20080701' THEN '2nd Qtr 08'
>> WHEN me_date >= '20080701' AND me_date < '20081001' THEN '2nd Qtr 08'
>> WHEN me_date >= '20081001' AND me_date < '20090101' THEN '2nd Qtr 08'
>> END AS Qtr,
>> Me_Date
>> FROM dbo.tblMeetings
>>
>> etc
>>
>> Also, the TOP (100) PERCENT is meaningless and there is no reason for it
>> to be there.
>>
>> Tom
>>
>> "SF" wrote in message
>> news:e3qYjpMCJHA.1816-at-TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> I have the foolowing view for grouping info by Quarter. I got some null
>>> value in the Qtr column when the ME_Date is
>>> 31/3/2008 11:46:18 AM and 30/6/2007 7:04:05 PM. It appears that these
>>> dates have not been converted to Qtr. Am I missing something?
>>>
>>> SELECT TOP (100) PERCENT Me_MeetingID AS [Number Of Meeting], CASE
>>> WHEN me_date BETWEEN '20080101' AND
>>> '20080331' THEN '1st Qtr 08' WHEN me_date BETWEEN
>>> '20080401' AND '20080630' THEN '2nd Qtr 08' WHEN me_date BETWEEN
>>> '20080601' AND
>>> '20080930' THEN '3rd Qtr 08' WHEN me_date BETWEEN
>>> '20081001' AND '20081231' THEN '4th Qtr 08' WHEN me_date BETWEEN
>>> '20070101' AND
>>> '20070331' THEN '1st Qtr 07' WHEN me_date BETWEEN
>>> '20070401' AND '20070630' THEN '2nd Qtr 07' WHEN me_date BETWEEN
>>> '20070701' AND
>>> '20070930' THEN '3rd Qtr 07' WHEN me_date BETWEEN
>>> '20071001' AND '20071231' THEN '4th Qtr 07' END AS Qtr, Me_Date
>>> FROM dbo.tblMeetings
>>> ORDER BY Qtr
>>>
>>>
>>> Could someone advice?
>>>
>>> SF
>>>
>>
>>
>
>