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.
| Author |
Topic |
|
mibs
Starting Member
3 Posts |
 Posted - 2012-03-05 : 09:50:22
|
| its quite confusing when it comes to dates (for me)Delete From Table where date < '01/31/2001'the above script is deleting dates that's before 01/31/2001, which is 01/30/2001 and so on right?Delete From Table where date > '01/31/2001'and this will be deleting dates that's after 01/31/2001, which is 02/01/2011 right? |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-03-05 : 09:56:14
|
use ISO standard dates and there can be no confusion.'YYYYMMDD' (example '20120201' -- 1st February 2012)otherwise there is the potential for your locale to screw things up.so your example would becomeDELETE FROM <table> WHERE <dateColumn> > '20010131' Which would delete any row where <dateColumn> is greater than 31st January 2001.If this is a DATETIME column this will delete any row where <DateColumn> is greater than 31 January 2001 AT MIDNIGHT. So it would delete anything ON the 31st of JAn as well.the ISO standard for DATE TIMES is (the ms are optional)'YYYY-MM-DDTHH:MM:SS.msms' -- example '2012-01-13T12:05:00.000' is 13th of January 2012 at 12:05 PMCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
mibs
Starting Member
3 Posts |
Posted - 2012-03-05 : 22:10:46
|
| ok for the format im using is MM/dd/yyyyhow it determine the dates are greater than when its deleting (datecolumn) > 01/31/2001? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-05 : 22:28:46
|
quote:
Originally posted by mibs
its quite confusing when it comes to dates (for me)Delete From Table where date < '01/31/2001'the above script is deleting dates that's before 01/31/2001, which is 01/30/2001 and so on right?Delete From Table where date > '01/31/2001'and this will be deleting dates that's after 01/31/2001, which is 02/01/2011 right?
change the DELETE to SELECT and you will see what are the rows return. These will be the rows that will be deleted
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
mibs
Starting Member
3 Posts |
Posted - 2012-03-06 : 00:47:49
|
| thx khtan :D |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-03-06 : 04:13:52
|
quote:
Originally posted by mibs
ok for the format im using is MM/dd/yyyyhow it determine the dates are greater than when its deleting (datecolumn) > 01/31/2001?
Well then change it! Use ISO date formats.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Delete dates that is earlier or older