How to do a "year-to-date" SQL query where "year" commences in August?

[ITM]

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

[John Gilson]

"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

[ITM]

Many thanks for this!

I.

On Fri, 19 Dec 2003 13:10:53 GMT, "John Gilson" <ja*@acm.org> wrote:

"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