|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
selecting from a 6 month date rangeSQL Server 2000 and T-sql, uk date formats I have a table, thetable. One of the fields in the table is tran_date which has type of datetime. I have to run a select on the table twice a year. Once after 31st Dec and once after 30 June. The select after 30th June must select all rows with a tran_date from 1st Jan - 30th June of the same year. The select run after 31st December must select all rows with a tran_date in the 6 month period from 1st July - 31st Dec on the year just past. Has anyone got any ideas on how to select the window required? The obvious answer might be for example on or after 1st Jan 2006 to use "select * from thetable where tran_date between 01/07/2005 and 31/12/2005" But this will be no good on 1st Jan 2007, it has to be dynamic and there is no guarantee that the select will be run sweet on 1st Jan or 1st July. Can anyone point me in the direction on how to code this? Thanks, Ruairi www.pocketpcheaven.com Something along these lines works, i use this for previous quarter
reporting... declare @qtr_start int, @qtr_finish int declare @dt_qtr_start datetime, @dt_qtr_finish datetime declare @yr char(4) set @yr = datename( year, getdate() ) declare @now datetime set @now = dateadd( month, -1, getdate() ) -- will give us previous quarter set @qtr_start = case when @now >= '1 jan ' + @yr and @now < '1 Apr ' + @yr then @yr + '0101' when @now >= '1 apr ' + @yr and @now < '1 jul ' + @yr then @yr + '0401' when @now >= '1 jul ' + @yr and @now < '1 oct ' + @yr then @yr + '0701' else @yr + '1001' end set @qtr_finish = case when @now >= '1 jan ' + @yr and @now < '1 Apr ' + @yr then @yr + '0331' when @now >= '1 apr ' + @yr and @now < '1 jul ' + @yr then @yr + '0631' when @now >= '1 jul ' + @yr and @now < '1 oct ' + @yr then @yr + '0931' else @yr + '1231' end set @dt_qtr_start = cast( cast( @qtr_start as char(8) ) + ' 00:00' as datetime ) set @dt_qtr_finish = cast( cast( @qtr_finish as char(8) ) + ' 00:00' as datetime ) Show quote "www.pocketpcheaven.com" <pr***@pocketpcheaven.com> wrote in message news:e50GmtT8FHA.2364@TK2MSFTNGP12.phx.gbl... > Hi all, > > SQL Server 2000 and T-sql, uk date formats > > I have a table, thetable. One of the fields in the table is tran_date > which has type of datetime. > > I have to run a select on the table twice a year. Once after 31st Dec and > once after 30 June. > > The select after 30th June must select all rows with a tran_date from 1st > Jan - 30th June of the same year. > > The select run after 31st December must select all rows with a tran_date > in the 6 month period from 1st July - 31st Dec on the year just past. > > Has anyone got any ideas on how to select the window required? The > obvious answer might be for example on or after 1st Jan 2006 to use > > "select * from thetable where tran_date between 01/07/2005 and 31/12/2005" > > But this will be no good on 1st Jan 2007, it has to be dynamic and there > is no guarantee that the select will be run sweet on 1st Jan or 1st July. > > Can anyone point me in the direction on how to code this? > > Thanks, > > Ruairi > www.pocketpcheaven.com > > > On Thu, 24 Nov 2005 21:10:05 +0000, www.pocketpcheaven.com wrote:
Show quote >Hi all, Hi Ruairi,> >SQL Server 2000 and T-sql, uk date formats > >I have a table, thetable. One of the fields in the table is tran_date >which has type of datetime. > >I have to run a select on the table twice a year. Once after 31st Dec >and once after 30 June. > >The select after 30th June must select all rows with a tran_date from >1st Jan - 30th June of the same year. > >The select run after 31st December must select all rows with a tran_date >in the 6 month period from 1st July - 31st Dec on the year just past. > >Has anyone got any ideas on how to select the window required? The >obvious answer might be for example on or after 1st Jan 2006 to use > >"select * from thetable where tran_date between 01/07/2005 and 31/12/2005" > >But this will be no good on 1st Jan 2007, it has to be dynamic and there >is no guarantee that the select will be run sweet on 1st Jan or 1st July. > >Can anyone point me in the direction on how to code this? > >Thanks, > >Ruairi >www.pocketpcheaven.com > > SELECT column list FROM thetable WHERE tran_date >= DATEADD(qq, DATEDIFF(qq, '20000101', getdate()) / 2 * 2, '20000101') AND tran_date < DATEADD(qq, DATEDIFF(qq, '20000101', getdate()) / 2 * 2, '20000701') Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Thanks Hugo. Your's is almost there...
If you run the following in query tantaliazer -- start code declare @thedate datetime set @thedate = getdate() select @thedate thedate select DATEADD(qq,DATEDIFF(qq, '20000101', @thedate) / 2 * 2,'20000101') AS thestart select DATEADD(qq,DATEDIFF(qq, '20000101', @thedate) / 2 * 2,'20000701') AS theend -- end code thestart is returning 1st July 2005 theend is returning 1st Jan 2006 Now that is what I *would* be looking for if I was running at after 31st Dec 2005 and before 01 July 2006 if you get my drift. For todays date 24 Nov 2005 when run the start and end should be returning 01 Jan 2005 as start and 01 July 2005 as end. Basically... if select date is after 30 June and on or before 31st Dec then window = 01 Jan-30 June of *current* year if select date is after 31st Dec and on or before 30 June then window = 01 July - 31st Dec of *previous* year Thanks for your help, Ruairi Hugo Kornelis wrote: Show quote > On Thu, 24 Nov 2005 21:10:05 +0000, www.pocketpcheaven.com wrote: > > >>Hi all, >> >>SQL Server 2000 and T-sql, uk date formats >> >>I have a table, thetable. One of the fields in the table is tran_date >>which has type of datetime. >> >>I have to run a select on the table twice a year. Once after 31st Dec >>and once after 30 June. >> >>The select after 30th June must select all rows with a tran_date from >>1st Jan - 30th June of the same year. >> >>The select run after 31st December must select all rows with a tran_date >>in the 6 month period from 1st July - 31st Dec on the year just past. >> >>Has anyone got any ideas on how to select the window required? The >>obvious answer might be for example on or after 1st Jan 2006 to use >> >>"select * from thetable where tran_date between 01/07/2005 and 31/12/2005" >> >>But this will be no good on 1st Jan 2007, it has to be dynamic and there >>is no guarantee that the select will be run sweet on 1st Jan or 1st July. >> >>Can anyone point me in the direction on how to code this? >> >>Thanks, >> >>Ruairi >>www.pocketpcheaven.com >> >> > > > Hi Ruairi, > > SELECT column list > FROM thetable > WHERE tran_date >= DATEADD(qq, > DATEDIFF(qq, '20000101', getdate()) / 2 * 2, > '20000101') > AND tran_date < DATEADD(qq, > DATEDIFF(qq, '20000101', getdate()) / 2 * 2, > '20000701') > > > Best, Hugo On Thu, 24 Nov 2005 23:15:04 +0000, www.pocketpcheaven.com wrote:
Show quote >Thanks Hugo. Your's is almost there... Hi Ruairi,> >If you run the following in query tantaliazer > >-- start code >declare @thedate datetime >set @thedate = getdate() > >select @thedate thedate >select DATEADD(qq,DATEDIFF(qq, '20000101', @thedate) / 2 * 2,'20000101') >AS thestart >select DATEADD(qq,DATEDIFF(qq, '20000101', @thedate) / 2 * 2,'20000701') >AS theend >-- end code > > > >thestart is returning 1st July 2005 >theend is returning 1st Jan 2006 My bad. Here's the code you need: select DATEADD(qq,DATEDIFF(qq, '20000701', @thedate) / 2 * 2,'20000101') AS thestart select DATEADD(qq,DATEDIFF(qq, '20000701', @thedate) / 2 * 2,'20000701') AS theend Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)
www.pocketpcheaven.com wrote:
> But this will be no good on 1st Jan 2007, it has to be dynamic and I think this one should do it:> there is no guarantee that the select will be run sweet on 1st Jan or > 1st July. > > Can anyone point me in the direction on how to code this? SELECT column list FROM thetable where case when DatePart(qq, getdate()) in (3, 4) then case when DatePart(qq, tran_date) in (1, 2) and year(tran_date) = year(getdate()) then 1 else 0 end else case when DatePart(qq, tran_date) in (3, 4) and year(tran_date) = year(getdate()) - 1 then 1 else 0 end end = 1 -- HTH, Stijn Verrept.  
Other interesting topics
|
|||||||||||||||||||||||
