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
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2013-06-21 : 05:12:02
That did the trick

Thanks a lot
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -