DateTime Add and Return - 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

SQL Server 2005 Forums

Transact-SQL (2005)

DateTime Add and Return

Author

Topic

bhanu2217
Starting Member

35 Posts

Posted - 2009-11-30 : 05:13:47

what i have to do is, get a date from user and compared it with the date stored in db_delivery_details(delivery_date) table where status='Issued'
before i compare i need to check in db_registration table what delivery_type of user is.

If(delivery_type = 10 days) than add 10 days to the date of delivery_details.

If(delivery_type = 15 days) than add 15 days to the date of delivery_details.

If(delivery_type = 30 days) than add 30 days to the date of delivery_details.

Once the date is added, i need to compare this date with user entered date, if it matches than return the result.

I have 2 tables.

Table1
db_delivery_details.
fk_mem_id varchar
delivery_date varchar(DD-MM-YYYY)

Table2
db_registration
userid varchar
delivery_type varchar

fk_mem_id and userid are primary key/Foreign Key

so basically i need a select query to do all this job.
I tried writing stored procedure for this as given below but i am kind of lost now. I am new to sql programming.
Date is is varchar DD-MM-YYYY format.

Here is my stored procedure, please let me know where i am going wrong.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROC dbo.[GetIncrementedDate]   '17-11-2009'
@deliveryDate VARCHAR(64)
AS
BEGIN
	SET NoCOunt ON 

	DECLARE @ReqDate		DATETIME
	DECLARE @ReqDate1		DATETIME
	DECLARE @noOfDays		VARCHAR(20)
	DECLARE @SQL			VARCHAR(1024)

	--Gets the date in the declared variable
	set dateformat dmy SELECT @ReqDate = CAST(delivery_end_date AS DATETIME), @noOfDays = delivery_type FROM db_registration Reg JOIN db_delivery_details DelDet
	ON userid=fk_mem_id 
	WHERE DelDet.status='Issued'

	IF @noOfDays = '10 Days'
	BEGIN
		set dateformat dmy SELECT @ReqDate1 = dateadd(day,10,cast(@ReqDate as datetime))
	END
    IF @noOfDays = '15 Days'
	BEGIN
		set dateformat dmy SELECT @ReqDate1 = dateadd(day,15,cast(@ReqDate as datetime))
	END
    IF @noOfDays = '30 Days'
	BEGIN
		set dateformat dmy SELECT @ReqDate1 = dateadd(day,30,cast(@ReqDate as datetime))
	END

    IF @ReqDate1 = CONVERT(DATETIME,@deliveryDate)
	BEGIN
		SET @SQL =  'SELECT * FROM db_registration Reg JOIN db_delivery_details DelDet
		ON userid=fk_mem_id 
		WHERE DelDet.status=''Issued'''-- AND @ReqDate1 = @deliveryDate
		SELECT @SQL
	END
	ELSE
	BEGIN
		SET @SQL = 'SELECT 0'
	END
END

www.JamboreeBliss.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-30 : 06:04:21

[code]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROC dbo.[GetIncrementedDate] '17-11-2009'
@deliveryDate VARCHAR(64)
AS
BEGIN
SET NoCOunt ON

DECLARE @ReqDate DATETIME
DECLARE @ReqDate1 DATETIME
DECLARE @noOfDays VARCHAR(20)
DECLARE @SQL VARCHAR(1024)

--Gets the date in the declared variable
set dateformat dmy SELECT @ReqDate = CAST(delivery_end_date AS DATETIME), @noOfDays = delivery_type FROM db_registration Reg JOIN db_delivery_details DelDet
ON userid=fk_mem_id
WHERE DelDet.status='Issued'
set dateformat dmy
SELECT @ReqDate1 = dateadd(day,replace(@noofdays,' days','')*1,@ReqDate)
SELECT * FROM db_registration Reg JOIN db_delivery_details DelDet
ON userid=fk_mem_id
WHERE DelDet.status='Issued'AND @deliveryDate = @ReqDate1

[/code]

Madhivanan

Failing to plan is Planning to fail

bhanu2217
Starting Member

35 Posts

Posted - 2009-11-30 : 08:50:21

quote:

Originally posted by madhivanan


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROC dbo.[GetIncrementedDate]   '17-11-2009'
@deliveryDate VARCHAR(64)
AS
BEGIN
	SET NoCOunt ON 

	DECLARE @ReqDate		DATETIME
	DECLARE @ReqDate1		DATETIME
	DECLARE @noOfDays		VARCHAR(20)
	DECLARE @SQL			VARCHAR(1024)

	--Gets the date in the declared variable
	set dateformat dmy SELECT @ReqDate = CAST(delivery_end_date AS DATETIME), @noOfDays = delivery_type FROM db_registration Reg JOIN db_delivery_details DelDet
	ON userid=fk_mem_id 
	WHERE DelDet.status='Issued'
	set dateformat dmy 
	SELECT @ReqDate1 = dateadd(day,replace(@noofdays,' days','')*1,@ReqDate)
	SELECT * FROM db_registration Reg JOIN db_delivery_details DelDet
	ON userid=fk_mem_id 
	WHERE DelDet.status='Issued'AND @deliveryDate = @ReqDate1

Madhivanan

Failing to plan is Planning to fail

Thanks for your help.
I made few changes, instead of adding days i am subtracting it now.
Its working but i have a problem.

Here is my stored procedure:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--dbo.[GetIncrementedDate1]   '17-11-2009'
ALTER PROC dbo.[GetIncrementedDate1]   --'05-12-2009'
@deliveryDate VARCHAR(64)
AS
BEGIN
	SET NoCOunt ON 

	DECLARE @ReqDate		DATETIME
	DECLARE @ReqDate1		DATETIME
	DECLARE @noOfDays		VARCHAR(20)
	DECLARE @SQL			VARCHAR(1024)

	--Gets the date in the declared variable
	set dateformat dmy SELECT @ReqDate = CAST(DelDet.delivery_date AS DATETIME), @noOfDays = Reg.delivery_type FROM db_registration Reg JOIN db_delivery_details DelDet
	ON Reg.userid=DelDet.fk_mem_id 
	WHERE DelDet.status='Issued'
	--set dateformat dmy 
	--SELECT @ReqDate1 = dateadd(day,replace(10,'days','')*1,@ReqDate)
	--set @ReqDate1 = dateadd(day,0,@ReqDate)
    --set dateformat dmy select @ReqDate1, @ReqDate,CAST(@deliveryDate AS DATETIME)
select @noOfDays
	
	IF @noOfDays = '10 Days'
	BEGIN
		set dateformat dmy SELECT * FROM db_registration Reg JOIN db_delivery_details DelDet
	ON Reg.userid=DelDet.fk_mem_id 
    WHERE DelDet.status='Issued' AND CAST(delivery_date AS DATETIME) = dateadd(day,-10,CAST(@deliveryDate AS DATETIME))
	END
	ELSE IF @noOfDays = '15 Days'
	BEGIN
		set dateformat dmy SELECT * FROM db_registration Reg JOIN db_delivery_details DelDet
	ON Reg.userid=DelDet.fk_mem_id 
    WHERE DelDet.status='Issued' AND CAST(delivery_date AS DATETIME) = dateadd(day,-15,CAST(@deliveryDate AS DATETIME))
	END
	ELSE IF @noOfDays = '30 Days'
	BEGIN
		set dateformat dmy SELECT * FROM db_registration Reg JOIN db_delivery_details DelDet
	ON Reg.userid=DelDet.fk_mem_id 
    WHERE DelDet.status='Issued' AND CAST(delivery_date AS DATETIME) = dateadd(day,-30,CAST(@deliveryDate AS DATETIME))
	END
--	 set dateformat dmy SELECT * FROM db_registration Reg JOIN db_delivery_details DelDet
--	ON Reg.userid=DelDet.fk_mem_id 
--    WHERE DelDet.status='Issued' AND CAST(delivery_date AS DATETIME) = dateadd(day,-10,CAST(@deliveryDate AS DATETIME))
	--WHERE DelDet.status='Issued' AND CAST(@deliveryDate AS DATETIME) = @ReqDate1
   -- WHERE DelDet.status='Issued' AND CAST('25-11-2009' AS DATETIME) = '25-11-2009'
END

The problem is here


set dateformat dmy SELECT @ReqDate = CAST(DelDet.delivery_date AS DATETIME), @noOfDays = Reg.delivery_type FROM db_registration Reg JOIN db_delivery_details DelDet
	ON Reg.userid=DelDet.fk_mem_id 
	WHERE DelDet.status='Issued'

This query returns multiple rows but i am able to access only the last row value from @ReqDate and @noOfDays. so not sure but i guess i need something like a while or for loop here to access multiple rows.

www.JamboreeBliss.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 10:02:15

thats because you're using variables to store values which can store only a single value. what you need is to store them in a table variable or temporary table and then join onto that in main query

bhanu2217
Starting Member

35 Posts

Posted - 2009-11-30 : 23:36:07

quote:
Originally posted by visakh16

thats because you're using variables to store values which can store only a single value. what you need is to store them in a table variable or temporary table and then join onto that in main query

Thank You.

www.JamboreeBliss.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources