Date Questions
in [Programming SQL]
|
Prev: THANKS!! select statement - multiple values returned
Next: How to construct check constraint to allow only one 'True' flag per vendor
From: Rain on 5 Jul 2008 15:04 Basically, I am trying to get invoices in a specific finacial year. See below. So, if I start with 2008 I will get 2008/2009 between 6th April 2008, and 5th April 2009. declare @targetDate as int; set @targetDate = 2008; select * from Invoice Where InvoiceDate Between cast(cast(@targetDate As nchar) + '/04/06' as datetime )AND cast(cast(@targetDate+1 As nchar) + '/04/05' as datetime ) order by InvoiceDate Asc I would like something more rubust which will allow me to create two dates based on information which is not globally changeable something like this following pseudo code. select * from invoice where InvoiceDate Between DateFunction( @targetDate,'MM','dd') and DateFunction( @targetDate,'MM','dd') Any help would be appreciated. - Cheers
From: John Bell on 5 Jul 2008 15:20 "Rain" <me(a)myplace.com> wrote in message news:O3TjnJt3IHA.4856(a)TK2MSFTNGP02.phx.gbl... > Basically, I am trying to get invoices in a specific finacial year. See > below. So, if I start with 2008 I will get 2008/2009 between 6th April > 2008, and 5th April 2009. > > declare @targetDate as int; > > set @targetDate = 2008; > > select * from Invoice Where InvoiceDate Between > > cast(cast(@targetDate As nchar) + '/04/06' as datetime )AND > > cast(cast(@targetDate+1 As nchar) + '/04/05' as datetime ) > > order by InvoiceDate Asc > > > > I would like something more rubust which will allow me to create two dates > based on information which is not globally changeable something like this > following pseudo code. > > select * from invoice where InvoiceDate Between DateFunction( > @targetDate,'MM','dd') and DateFunction( @targetDate,'MM','dd') > > Any help would be appreciated. - Cheers > Hi You may want to consider a calendar table, which can be pre-populated with financial periods, working days etc... see http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html for more John
From: Rain on 5 Jul 2008 15:30 Thanks John, I will look at that, in the meantime, do you know how I can construct my query as explained. Regards - Rain "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message news:B3DB6EA3-4B5E-42B6-B0C3-0D5D63A97BE4(a)microsoft.com... > > "Rain" <me(a)myplace.com> wrote in message > news:O3TjnJt3IHA.4856(a)TK2MSFTNGP02.phx.gbl... >> Basically, I am trying to get invoices in a specific finacial year. See >> below. So, if I start with 2008 I will get 2008/2009 between 6th April >> 2008, and 5th April 2009. >> >> declare @targetDate as int; >> >> set @targetDate = 2008; >> >> select * from Invoice Where InvoiceDate Between >> >> cast(cast(@targetDate As nchar) + '/04/06' as datetime )AND >> >> cast(cast(@targetDate+1 As nchar) + '/04/05' as datetime ) >> >> order by InvoiceDate Asc >> >> >> >> I would like something more rubust which will allow me to create two >> dates based on information which is not globally changeable something >> like this following pseudo code. >> >> select * from invoice where InvoiceDate Between DateFunction( >> @targetDate,'MM','dd') and DateFunction( @targetDate,'MM','dd') >> >> Any help would be appreciated. - Cheers >> > Hi > > You may want to consider a calendar table, which can be pre-populated with > financial periods, working days etc... see > > http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html > > for more > > John
From: John Bell on 5 Jul 2008 16:14 "Rain" <me(a)myplace.com> wrote in message news:upMtKYt3IHA.2348(a)TK2MSFTNGP06.phx.gbl... > Thanks John, I will look at that, in the meantime, do you know how I can > construct my query as explained. > > Regards - Rain > > > "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message > news:B3DB6EA3-4B5E-42B6-B0C3-0D5D63A97BE4(a)microsoft.com... >> >> "Rain" <me(a)myplace.com> wrote in message >> news:O3TjnJt3IHA.4856(a)TK2MSFTNGP02.phx.gbl... >>> Basically, I am trying to get invoices in a specific finacial year. See >>> below. So, if I start with 2008 I will get 2008/2009 between 6th April >>> 2008, and 5th April 2009. >>> >>> declare @targetDate as int; >>> >>> set @targetDate = 2008; >>> >>> select * from Invoice Where InvoiceDate Between >>> >>> cast(cast(@targetDate As nchar) + '/04/06' as datetime )AND >>> >>> cast(cast(@targetDate+1 As nchar) + '/04/05' as datetime ) >>> >>> order by InvoiceDate Asc >>> >>> >>> >>> I would like something more rubust which will allow me to create two >>> dates based on information which is not globally changeable something >>> like this following pseudo code. >>> >>> select * from invoice where InvoiceDate Between DateFunction( >>> @targetDate,'MM','dd') and DateFunction( @targetDate,'MM','dd') >>> >>> Any help would be appreciated. - Cheers >>> >> Hi >> >> You may want to consider a calendar table, which can be pre-populated >> with financial periods, working days etc... see >> >> http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html >> >> for more >> >> John In the first query as you specify target start date you may as well specify the end date in full DECLARE @targetStartDate as datetime, @targetEndDate datetime SELECT @targetStartDate = '20080406', @targetEndDate = '20090406' SELECT * FROM Invoice WHERE InvoiceDate >= @targetStartDate AND InvoiceDate < @targetEndDate ORDER BY InvoiceDate Asc I assume that you expected the end date to be inclusive to '20090405 23:59:59.999' so you can actual use excluse '20090406 00:00:00.000'' which is just one year after the start date DECLARE @targetStartDate as datetime, @targetEndDate datetime SET @targetStartDate = '20080406' SET @targetEndDate = DATEADD(yy,1,@targetStartDate) John
From: Erland Sommarskog on 5 Jul 2008 18:16
John Bell (jbellnewsposts(a)hotmail.com) writes: > In the first query as you specify target start date you may as well > specify the end date in full > > DECLARE @targetStartDate as datetime, @targetEndDate datetime > SELECT @targetStartDate = '20080406', @targetEndDate = '20090406' > > SELECT * > FROM Invoice > WHERE InvoiceDate >= @targetStartDate > AND InvoiceDate < @targetEndDate > ORDER BY InvoiceDate Asc > > I assume that you expected the end date to be inclusive to '20090405 > 23:59:59.999' so you can actual use excluse '20090406 00:00:00.000'' > which is just one year after the start date > > DECLARE @targetStartDate as datetime, @targetEndDate datetime > SET @targetStartDate = '20080406' > SET @targetEndDate = DATEADD(yy,1,@targetStartDate) I would assume that Rain's invoices does not have any time portion at all, at least I hope so! In the case, I think that this is the best given that the year was in a parameter: declare @targetDate as int; set @targetDate = 2008; select * from Invoice Where InvoiceDate >= ltrim(str(@targetDate)) + '0406' AND InvoiceDate < dateadd(YEAR, 1, ltrim(str(@targetDate)) + '0406') order by InvoiceDate Asc That is, change BETWEEN to >= and <. And use YYYYMMDD as date format to avoid issues with different DATEFORMAT settings. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |