"ITM" <it*@manning.uk.com> wrote in message news:pb********************************@4ax.com...
Does anyone have an example of an SQL query which returns rows for the
year-to-date, but where the "year" commences on August 1st?
e.g. select * from mytable where datefield > last august 1st
TIA for any help
Isabel
CREATE TABLE T
(
d DATETIME NOT NULL PRIMARY KEY
)
-- Sample data
INSERT INTO T (d)
VALUES ('20030801')
INSERT INTO T (d)
VALUES ('20030901')
INSERT INTO T (d)
VALUES ('20030501')
INSERT INTO T (d)
VALUES ('20021201')
-- For current date
SELECT d
FROM T
WHERE (MONTH(CURRENT_TIMESTAMP) >= 8 OR
d >= CAST(YEAR(CURRENT_TIMESTAMP) - 1 AS CHAR(4)) + '0801')
AND
(MONTH(CURRENT_TIMESTAMP) < 8 OR
d >= CAST(YEAR(CURRENT_TIMESTAMP) AS CHAR(4)) + '0801')
ORDER BY d
d
2003-08-01 00:00:00.000
2003-09-01 00:00:00.000
-- For provided date
CREATE FUNCTION YearToDate (@d DATETIME)
RETURNS TABLE
AS
RETURN(
SELECT d
FROM T
WHERE (MONTH(@d) >= 8 OR
(d BETWEEN
CAST(YEAR(@d) - 1 AS CHAR(4)) + '0801' AND @d))
AND
(MONTH(@d) < 8 OR
(d BETWEEN CAST(YEAR(@d) AS CHAR(4)) + '0801' AND @d))
)
SELECT d
FROM YearToDate('20030701')
ORDER BY d
d
2002-12-01 00:00:00.000
2003-05-01 00:00:00.000
Regards,
jag
