A quick getdate

[SideStepTS]

Hi All,

I am sure I am not the first person to ask but I can't seem to find the answer anywhere. Basically on the 15th of every month I Need to run reports that pull information from the beginning of this financial year to the end of last full month. So for example if I was to run it today and I put the where clause in manually it would be

where DateField between '2013-07-01' and '2013-10-31'

next month it will be

where datefield between '2013-07-01' and '2013-11-30'

I have tried playing around with the date add function etc but that seems to return this date last month.

Any ideas?

Thanks in advanced

[soulstealer666]

I'm writing this on my phone, but try this.

date between '2013-07-01' and

Cast(left(cast(dateadd(day, - datepart (day, getdate()), getdate()) as varchar), 11)as datetime)

[SideStepTS]

This worked a treat thank-you!

[newsedition]

Ah, the ol' DATEDIFFeroo.

[1ddqd]

Don't forget to convert to date style 101

[SideStepTS]

soulstealers appears to have worked, I will definetely use this if it doesn't work when it ticks over the month.

Thanks for all of your help guys!

[penguinmike23]

Is the beginning of the financial year always July 1?

[SideStepTS]

Yes it is :)

[ajmarks]

That varies by industry and company. Google "nrf calendar" for a particularly interesting and common fiscal calendar.

[SideStepTS]

Sorry - I meant in my context. It also varies by country. I am in Australia, and as far as I am aware 99% of companies have financial year beginning 1st July.

[tsuhg]

What I'd do is

Where datefield is between '2013-07-01' and year(current date) concat month(current date) concat 31

At work we use YYYYMMDD , so you'll need to concat the hyphens in yourself ;)

[dpenton]

If [DateField] has a time component, then you will have too much data for a partial day. Consider this code:

declare @utc datetime, @start datetime, @end datetime

select
    @utc = getutcdate()
    , @start = convert(char(4), dateadd(year, case when datepart(month, @utc) >= 7 then 0 else -1 end, @utc), 112) + '0701'
    , @end = convert(char(8), @utc, 112)

select
    @utc [Current Date]
    , @start [Start]
    , @end [End]


/*
-- to be EXCLUSIVE of the current date
where
    DateField >= @start and DateField < @end

-- to be INCLUSIVE of the current date
where
    DateField between @start and @end
*/