Problem with update and date - 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)

Problem with update and date

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-17 : 11:51:07

Hi,

I am having many problems lately with dates as you might have noticed. My problem now is that I have to update a record in the database.
The sp is as follows:



ALTER procedure  [dbo].[sp_upd_advertisment]
@Cost int,
@User_id int,
@Advertising_Code nvarchar(20),
@From_date datetime ,--nvarchar(8),
@To_Date datetime,--nvarchar(8),
@Country_id int
,@Default_code bit
,@Active bit
as
UPDATE Advertisment
 SET Cost = @Cost,
 Date_Updated = convert(datetime,getdate(),103),
 User_Id_Upd = @User_id
 WHERE  ((Advertising_Code = @Advertising_Code ) AND 
          (Country_id = @Country_id) AND 
          (From_date = convert(datetime,@From_date,3)) AND 
          (To_Date = convert(datetime,@To_Date,3)))

If i execute the sp as follows than zero rows are effected:
exec sp_upd_advertisment
@Cost=7000,@User_id=N'990',@Default_code=0,@Active=0,@Advertising_Code=N'AUSCV',@Country_id=3,@From_date=''2007-10-31
00:00:00:000'',@To_date=''2007-10-16 00:00:00:000''

This is an example of the existing data in the db that i am trying to update:
Advertising_Code|country_id|from_date|to_Date|cost
AUSCV |3|2007-10-31 00:00:00.000|2007-10-16 17:47:59.000|3000

As you can see the to_Date field contains the date as 2007-10-16 17:47:59.000 and the value of the parameter @to_Date is ''2007-10-16 00:00:00:000'' so that is why the row isn't updated. How can i solve the problem? Maybe ignore the time part?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 11:53:22

How come ToDate is earlier than FromDate?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 11:57:28

[code]ALTER PROCEDURE [dbo].[sp_upd_advertisment]
(
@Cost INT,
@User_id INT,
@Advertising_Code NVARCHAR(20),
@From_date DATETIME,
@To_Date DATETIME,
@Country_id INT,
@Default_code BIT,
@Active BIT
)
AS

SET NOCOUNT ON

UPDATE Advertisment
SET Cost = @Cost,
Date_Updated = DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101'),
User_Id_Upd = @User_id
WHERE Advertising_Code = @Advertising_Code
AND Country_id = @Country_id
AND From_date >= DATEADD(DAY, DATEDIFF(DAY, '19000101', @From_Date), '19000101')
AND From_date < DATEADD(DAY, DATEDIFF(DAY, '18991231', @From_Date), '19000101')
AND To_Date >= DATEADD(DAY, DATEDIFF(DAY, '19000101', @To_Date), '19000101'
AND To_Date < DATEADD(DAY, DATEDIFF(DAY, '18991231', @To_Date), '19000101')[/code]

E 12°55'05.25"
N 56°04'39.16"

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-18 : 06:36:09

HI,

Yes, the to_date must be earlier.
Anyway, thanks for modifying my sp- it works great :-)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 06:38:54

DATEADD(DAY, DATEDIFF(DAY, '........', @From_Date), '........')

You're going to need a UDF for that soon ...

Here's one I prepared earlier:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62354

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources