By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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
Jul 20 '05 #2

P: n/a
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


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.