SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 date update problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 06/21/2013 :  04:15:24  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

India
52317 Posts

Posted - 06/21/2013 :  04:27:44  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/21/2013 04:28:10
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 06/21/2013 :  05:12:02  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
That did the trick

Thanks a lot
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  05:13:11  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000