I sent the following message on Friday and it was rejected
for some strange reason, so I am reposting it.  I apologize
in advance if you have already recieved this message.

Cardyin


Doing a string comparison for your date is
very slow compared to a comparison done
on an integer value.  Also, using
any kind of string manipulation on
a date is not recommended since
the displayed date depends on the short and
long format of the server that you are using.

To be sure that you get the apporpriate date
that you want 100% of the time, you must
avoid using any string functions at all on dates.

SELECT CAST(MONTH(DateFrom) AS VARCHAR) + '/' + 
CAST(YEAR(DateFrom) AS VARCHAR), SUM(Price) AS [Monthly Sales],
SUM(Deposit) AS [Total Deposit] FROM Booking WHERE
((MONTH(DateFrom) = 11 AND YEAR(DateFrom) = 2001) OR
(MONTH(DateFrom) = 12 AND YEAR(DateFrom) = 2001))
AND (statuscode=1020 OR statuscode=1030 OR statuscode=1060) 
GROUP BY CAST(MONTH(DateFrom) AS VARCHAR) + '/' + 
CAST(YEAR(DateFrom) AS VARCHAR)
ORDER BY CAST(MONTH(DateFrom) AS VARCHAR) + '/' + 
CAST(YEAR(DateFrom) AS VARCHAR)

This query should work to fit your needs.  Here is
a version of the query that should work a little
faster:

SELECT CAST(MONTH(DateFrom) AS VARCHAR) + '/' + 
CAST(YEAR(DateFrom) AS VARCHAR), SUM(Price) AS [Monthly Sales],
SUM(Deposit) AS [Total Deposit] FROM Booking WHERE
(DateFrom >= '11/1/2001' AND DateFrom < '1/1/2002')
AND (statuscode=1020 OR statuscode=1030 OR statuscode=1060) 
GROUP BY CAST(MONTH(DateFrom) AS VARCHAR) + '/' + 
CAST(YEAR(DateFrom) AS VARCHAR)
ORDER BY CAST(MONTH(DateFrom) AS VARCHAR) + '/' + 
CAST(YEAR(DateFrom) AS VARCHAR)

Hope this helps you.  Happy Holidays!

Cardyin

---------------------------------------
Cardyin Kim
Client/Server & Web Development Analyst
Information Services
San Antonio Community Hospital
ckim@s...     (xxx)xxx-xxxx     
---------------------------------------


-----Original Message-----
From: Ola [mailto:ola@o...]
Sent: Friday, December 21, 2001 9:24 AM
To: professional vb
Subject: [pro_vb] Date Manipulation in SQL Server 7



Hello,

Help Me Please?????

The problem I have is with datetime data type in SQL Server 7 I am
trying to  create a query to calculate monthly sales I try using the
below SQL Statement but get errors that I cannot use the substring
function on  a datetime data type

I have tried mid function but this only works for Access

Any info on Converting Data types or how to fix this problem will be
greatly appreciated


SQL Statement

Select right(DateFrom,7) as [Month/Year],Sum(Price) as [Monthly Sales],
Sum(Deposit) as [Total Deposit] from booking  where (
substring(DateFrom,7,4)='11/2001' or substring(DateFrom,7,4)='12/2001' )
and ( statuscode=1020 or statuscode=1030  or statuscode=1060 ) group by
substring(DateFrom,7,4) order by substring(DateFrom,7,4) asc



Thanks in advance
Ola



to unsubscribe send a blank email to $subst('Email.Unsub')...