473,569 Members | 2,571 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Jul 20 '05 #1
2 23342
"ITM" <it*@manning.uk .com> wrote in message news:pb******** *************** *********@4ax.c om...
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(CURRE NT_TIMESTAMP) - 1 AS CHAR(4)) + '0801')
AND
(MONTH(CURRENT_ TIMESTAMP) < 8 OR
d >= CAST(YEAR(CURRE NT_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('200 30701')
ORDER BY d

d
2002-12-01 00:00:00.000
2003-05-01 00:00:00.000

Regards,
jag
Jul 20 '05 #2
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.c om...
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(CURRE NT_TIMESTAMP) - 1 AS CHAR(4)) + '0801')
AND
(MONTH(CURRENT_ TIMESTAMP) < 8 OR
d >= CAST(YEAR(CURRE NT_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('200 30701')
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
4386
by: LRW | last post by:
I'm not exactly sure how to even ask the question, and I know my terminology is not good as I'm a SQL beginner, but, here goes. I need to find a way to make an if statement within an array...or, the "while" portion of a recordset. The best way I can ask is show what I mean. http://oscarguy.mechphisto.net/awardbrowse.php If you go there...
4
2508
by: Phil Powell | last post by:
re: http://us4.php.net/manual/en/function.imagegif.php I have one class method that resizes images, including GIF images, and works just fine doing so, even up to this line: eval('image' . $extArray . '($newImage, "$tmpImageDownloadDir/" .. $this->fileName);'); // SAVE TO TEMPORARY IMAGE DIR FOR DOWNLOAD
16
2876
by: aurora | last post by:
Hello! Just gone though an article via Slashdot titled "The Free Lunch Is Over: A Fundamental Turn Toward Concurrency in Software" http://www.gotw.ca/publications/concurrency-ddj.htm]. It argues that the continous CPU performance gain we've seen is finally over. And that future gain would primary be in the area of software concurrency...
8
33896
by: Mario T. Lanza | last post by:
I'm not sure what I'm doing wrong. I have a form that has mnay input fields. Before each input field is a label enclosed in custom LABEL tags. Inside my CSS I have: LABEL { width: 120px; }
32
3365
by: Will Hartung | last post by:
Can someone clarify that multiple classes in the "class" attribute are ok and "legal" and not some fluke? So, I can do: ..pink {color: pink} ..bold {font-weight: bold} ..medium {font-size: 12pt} <p class="bold pink medium">bold pink medium sized text</p>
10
1823
by: ryankbrown | last post by:
I was wondering if anybody has a document that states do's and don'ts when programming for a MS SQL DB. I was under the impression that Microsoft once produced such a document. Rather than a "Best Practices" type document it was more of a "Worst Practices" document. Stating certain procedures that might create slow-downs, bottlenecks, etc. Any...
48
4714
by: mahurshi | last post by:
I am new to c++ classes. I defined this "cDie" class that would return a value between 1 and 6 (inclusive) It runs fine and gives no warnings during compilation. I was wondering if you guys can pick up any mistakes/"don't do"s from this code: #include <iostream> #include <cstdlib>
5
3418
by: Stewart | last post by:
Hi there, I would like to calculate a person's age in years (between 2 dates). I am using the following function for this calculation (source: http://www.mvps.org/access/datetime/date0001.htm) _________________________________________ Function Age(DateOfBirth, DateToday) As Integer ' Returns the Age in years between 2 dates
7
8508
by: Eric | last post by:
Hi For this code, int getopt (int argc, char *const argv, const char *opts) what does the "char *const argv" mean? Does it equal to "char **const argv"? Or "char *const *argv"? Which is the const? Thanks
5
3862
by: maury | last post by:
Hello, I have a DB table with data filled from a weather sensor probe, I have one row every 10 minutes and the data fields is not in DateTime format but in string format: yyyyMMddHHmm So for example I have 200804140340 200804140350 200804140400 200804140410
0
7625
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7935
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8144
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7692
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7992
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6313
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5519
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3677
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1235
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.