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 |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2005-07-14 : 13:45:48
|
| I have one column called due_date as varchar(8) and I want to check how many records are greater than getdate()....Its giving me wrong answerscolumn- due_dateselect emp_id,due_date from tbl_emp where due_date > CONVERT(varchar(10),getdate(),101)Its comparing as string..so getting wrong answersThe values in the columns are as follows'01/01/05'empty |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-14 : 13:52:28
|
| So if I have a due date in January next year that might be '01/31/2006'?If I test due_date > CONVERT(varchar(10),getdate(),101) that will be FALSE because '01/31/2006' is less than '07/14/2005' ["01" is less than "07"]You need to convert your date column to a datetime, and compare using that datatype - then they will be in chronological orderselect emp_id,due_date from tbl_emp where CONVERT(datetime, due_date) > getdate()However, this will give errors if your text "due_date" column contains invalid dates.Much better to always store dates in a datetime datatype column, not as "text"Kristen |
 |
|
|
|
|
|
Comparing dates