Date Range help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Range help needed

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-02-06 : 02:04:53

Hi Friends,
My table structure is as below:
select inv_date from Test

from this table, i need to populate based on the input criteria
Pseudocode:
if the input is 'Past Due' then it should be column_name <GETdate()
else if input is 'Due this month' then it should be column_name=current_month (ie feb-09)
else if input is 'Due this Qtr' then it should be column_name=current_qtr (ie apr,may,jun)
else if input is 'Due next month' then it should be column_name=next_month(ie mar-09)
else if input is 'Due next Qtr' then it should be column _name= next_qtr(apr,may,jun)
else
'Due this Year' then is should be column_name='apr-08 to mar-09'

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 03:14:15

[code]ALTER PROCEDURE dbo.uspGetMyData
(
@RangeChoice VARCHAR(20) = NULL
)
AS

SET NOCOUNT ON

DECLARE @FromDate DATETIME,
@ToDate DATETIME

IF @RangeChoice = 'Past due'
SELECT @FromDate = '17530101',
@ToDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')

IF @RangeChoice = 'Due this Qtr'
SELECT @FromDate = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000101'),
@ToDate = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000401')

IF @RangeChoice = 'Due next month'
SELECT @FromDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000201'),
@ToDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000301')

IF @RangeChoice = 'Due next Qtr'
SELECT @FromDate = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000401'),
@ToDate = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000701')

IF @FromDate IS NULL -- Default to Due this month
SELECT @FromDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101'),
@ToDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000201')

SELECT *
FROM Table1
WHERE Col1 >= @FromDate
AND Col1 < @ToDate[/code]

E 12°55'05.63"
N 56°04'39.26"