Date query

Hi, I'm new to SQL. I need to return a recordset which will include only
records with a date after previous June. (Could be this year or last) It's
for tax purposes. How can i specify that it's the last June, not 'June' as a
value.
Hope somebody can help. Many thanks for your ideas.
Ant
SELECT date,
payment
FROM table
WHERE "date is greater than beginning of this financial year"That would be a option to use a Fical table which is populate with the dates
of the fiscal year and the fiscal period, there for you could easily
determine the appopiate date within the fiscal year and join it to the data
table. So for you there would be not need to touch the condition on the data
table because the join would do all the work for you.
http://www.aspfaq.com/show.asp?id=2519
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Ant" <Ant@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E9F635A9-8517-49F3-A7C3-318B9D82BC22@.microsoft.com...
> Hi, I'm new to SQL. I need to return a recordset which will include only
> records with a date after previous June. (Could be this year or last) It's
> for tax purposes. How can i specify that it's the last June, not 'June' as
> a
> value.
> Hope somebody can help. Many thanks for your ideas.
> Ant
> SELECT date,
> payment
> FROM table
> WHERE "date is greater than beginning of this financial year"|||Hi Ant
Probably this can solve the problem
SELECT date, payment
FROM table
WHERE
date > (
SELECT '01/06/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
datepart(yy,getdate())-1
ELSE datepart(yy,getdate())
END as varchar(4)))
Please let me know if there is any thing that u wanted to know.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ant" wrote:

> Hi, I'm new to SQL. I need to return a recordset which will include only
> records with a date after previous June. (Could be this year or last) It's
> for tax purposes. How can i specify that it's the last June, not 'June' as
a
> value.
> Hope somebody can help. Many thanks for your ideas.
> Ant
> SELECT date,
> payment
> FROM table
> WHERE "date is greater than beginning of this financial year"|||Hello Chandra,
Thank you very much for your idea. It looks great. It almost works.
Unfortunately it returns records from January 31-2004, however the earliest
record should be no earlier than the previous June. I'll play around with it
but if you have any ideas, I'd be most grateful.
Many thanks
Ant
"Chandra" wrote:
> Hi Ant
> Probably this can solve the problem
>
> SELECT date, payment
> FROM table
> WHERE
> date > (
> SELECT '01/06/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> datepart(yy,getdate())-1
> ELSE datepart(yy,getdate())
> END as varchar(4)))
> Please let me know if there is any thing that u wanted to know.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Ant" wrote:
>|||Hi Ant
Then it might be with the date format.
just try this way.
FYI: i changed from 01/06 to 06/01
SELECT date, payment
FROM table
WHERE
date > (
SELECT '06/01/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
datepart(yy,getdate())-1
ELSE datepart(yy,getdate())
END as varchar(4)))
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ant" wrote:
> Hello Chandra,
> Thank you very much for your idea. It looks great. It almost works.
> Unfortunately it returns records from January 31-2004, however the earlies
t
> record should be no earlier than the previous June. I'll play around with
it
> but if you have any ideas, I'd be most grateful.
> Many thanks
> Ant
> "Chandra" wrote:
>|||Hi Chandra,
Thanks very much for that help. It works. I was actually thinking on the way
home, "I wonder if I change the date around..." But I would never have been
able to work out that date string in the WHERE clause. I understand Transact
SQL language but not good at working out queries like this one. Can you
recommend any good books on useful business queries that I might gain more
understanding by? Kind of techniques, rather than syntax. If anybody knows,
it would be great.
Chandra, Thanks heaps!
"Chandra" wrote:
> Hi Ant
> Then it might be with the date format.
> just try this way.
> FYI: i changed from 01/06 to 06/01
> SELECT date, payment
> FROM table
> WHERE
> date > (
> SELECT '06/01/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> datepart(yy,getdate())-1
> ELSE datepart(yy,getdate())
> END as varchar(4)))
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Ant" wrote:
>|||Hi Ant
I can understand your curiosity. I never came across and book that can give
you an idea on how to do it. Most of the books give u only the syntax.
To get more ideas on how to build business logic, u need to practice. Just
think of a situation and try to build the query for yourself. One place u ca
n
find to get a lot of ideas is newsgroups and user-groups.
There are also a lot of websites that are developed by MVPs. Just go through
them, u might pick up ideas and techniques.
Please let me know if you would like to know anything else
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ant" wrote:
> Hi Chandra,
> Thanks very much for that help. It works. I was actually thinking on the w
ay
> home, "I wonder if I change the date around..." But I would never have bee
n
> able to work out that date string in the WHERE clause. I understand Transa
ct
> SQL language but not good at working out queries like this one. Can you
> recommend any good books on useful business queries that I might gain more
> understanding by? Kind of techniques, rather than syntax. If anybody knows
,
> it would be great.
> Chandra, Thanks heaps!
>
> "Chandra" wrote:
>|||Thank you Chandra, you've been a great help. I'll keep on practising & check
out the News groups.
Cheers
Ant
"Chandra" wrote:
> Hi Ant
> I can understand your curiosity. I never came across and book that can giv
e
> you an idea on how to do it. Most of the books give u only the syntax.
> To get more ideas on how to build business logic, u need to practice. Just
> think of a situation and try to build the query for yourself. One place u
can
> find to get a lot of ideas is newsgroups and user-groups.
> There are also a lot of websites that are developed by MVPs. Just go throu
gh
> them, u might pick up ideas and techniques.
> Please let me know if you would like to know anything else
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Ant" wrote:
>sql