Compare 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)

Compare Date

Author

Topic

bhanu2217
Starting Member

35 Posts

Posted - 2009-12-14 : 11:31:52

Hi
I have written a stored procedure to compare dates but time is also get compared, but i just want to compare today's date with the date stored in my table, plz let me know where i am going wrong.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[spx_CheckLoginDate] 
      @UserName VARCHAR(50)
AS
BEGIN
      SET NOCOUNT ON;
	  Declare @table	TABLE(userid varchar(20),LastLogin datetime)
	  DECLARE @date		DATETIME
      Insert Into @table SELECT userid,LastLogin FROM db_login
             WHERE userid = @UserName 

select @date=  LastLogin from @table
IF @date <> getdate()
begin
update db_login set LastLogin= getdate() where userid=@UserName
update members set count=0 where userid=@UserName
end
           
END

www.JamboreeBliss.com

bhanu2217
Starting Member

35 Posts

Posted - 2009-12-14 : 11:37:53

Hi

I have solved the problem using this method


SELECT CAST(FLOOR(CAST( getdate() AS float)) AS datetime)

www.JamboreeBliss.com

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 12:15:54

In general, the query below is used to strip the time portion off of the getdate() or any datetime field.

SELECT dateadd(d, datediff(d, 0, getdate()), 0)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 12:21:15

Also, what are you doing with that stored procedure...doesn't seem all that right to me..

Why all the verification..if you are comparing the same fields(LastLogin)??

bhanu2217
Starting Member

35 Posts

Posted - 2009-12-14 : 12:30:34

quote:
Originally posted by vijayisonly

Also, what are you doing with that stored procedure...doesn't seem all that right to me..

Why all the verification..if you are comparing the same fields(LastLogin)??

I am new to stored procedure, so just trying my hand on it, plz feel to correct if i have gone wrong.

I am comparing the date stored in db_login table with today's date, if they don't match then i add today's date to this table and i also make the count =0 in members table.

www.JamboreeBliss.com

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 13:28:50

I'm not sure..but maybe you just need this? Try it out in a test environemnt and let us know.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[spx_CheckLoginDate] 
      @UserName VARCHAR(50)
AS
BEGIN

update db_login set LastLogin = getdate() 
where userid=@UserName 
and dateadd(d, datediff(d, 0, LastLogin), 0) <> dateadd(d, datediff(d, 0, getdate()), 0) 

update members set count=0 where userid=@UserName
           
END

bhanu2217
Starting Member

35 Posts

Posted - 2009-12-14 : 22:18:18

quote:

Originally posted by vijayisonly

I'm not sure..but maybe you just need this? Try it out in a test environemnt and let us know.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[spx_CheckLoginDate] 
      @UserName VARCHAR(50)
AS
BEGIN

update db_login set LastLogin = getdate() 
where userid=@UserName 
and dateadd(d, datediff(d, 0, LastLogin), 0) <> dateadd(d, datediff(d, 0, getdate()), 0) 

update members set count=0 where userid=@UserName
           
END

Thank You.

www.JamboreeBliss.com

olivia751
Starting Member

2 Posts

Posted - 2009-12-15 : 04:54:10

Hello,

Thank you all for giving here the solution fro the problem.
I also stuck in an example or cerating trigger. I also stuck in one place while create a trigger.

Regards,
Olivia

IT Disaster Recovery & Online Data Backup UK

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-15 : 09:59:31

You're welcome.

quote:
Originally posted by bhanu2217

quote:
Originally posted by vijayisonly

I'm not sure..but maybe you just need this? Try it out in a test environemnt and let us know.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[spx_CheckLoginDate]
@UserName VARCHAR(50)
AS
BEGIN

update db_login set LastLogin = getdate()
where userid=@UserName
and dateadd(d, datediff(d, 0, LastLogin), 0) <> dateadd(d, datediff(d, 0, getdate()), 0)

update members set count=0 where userid=@UserName

END

Thank You.

www.JamboreeBliss.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources