date update problem - 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)

date update problem

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-06-21 : 04:15:24

Hi all,

i got this statement, but not working how it should because of diffrent date structure

UPDATE T_Order_Coupon
SET active =0
WHERE expdate > GETDATE() and not expdate=null

in the DB i got field datetime and value is
5/15/2013 12:00:00 AM
but then when i do getdate()it returns
2013-06-21 10:07:39.140

How can i solve this?

Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 04:27:44

Again a case of not using proper datatype for the field. You should be using datetime as datatype for expdate field which will avoid all these issues

Anyways in the current way you can do something like below


UPDATE t
SET active =0
FROM (SELECT active,CONVERT(datetime,expdate,101) AS expdate 
FROM T_Order_Coupon)t
WHERE expdate > GETDATE()

the other condition is redundant as the above check itself will ensure expdate is not null

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mike13
Posting Yak Master

219 Posts

Posted - 2013-06-21 : 05:12:02

That did the trick

Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 05:13:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources