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 |
|
edpel
Starting Member
22 Posts |
 Posted - 2003-11-17 : 16:24:53
|
| Ok, I know this is probably an easy one but it is Monday after all. I have this statement:select * from tablename where birthdate <=DATEADD(year, -18, getdate())Which works fine but many of the values in the table have a time of 00:00:00.000 so it doesn't correctly evaluate agains getdate. I have tried converting the values to char(10) to drop the time but then it doesn't evaluate correctly either.select * from tablename where convert(char(10),birthdate,1) >=convert(char(10),(DATEADD(year, -17, getdate())),1)How can I simply truncate the time from the date field?Thanks,Eddie |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-17 : 16:28:15
|
| Using style 101 is the way to go but style 1 should have worked also. You changed <= to >= and also -18 to -17, so that may be your problem.SELECT *FROM Table1WHERE CONVERT(VARCHAR(50), birthdate, 101) <= CONVERT(VARCHAR(50), (DATEADD(year, -18, GETDATE())), 101)What do you want your query to do? There is most likely an easier solution.Tara |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-17 : 16:34:02
|
| Here is an easy way to calculate ages:SELECT DATEDIFF(dd, Birthdate,GETDATE())/365.00FROM Table1I just don't understand why you are adding -17 or -18 to the year part of the datetime column.Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-11-17 : 18:20:12
|
| Tara, probably trying to find adults, right?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-17 : 18:23:53
|
| That's what I figured, but I wasn't sure why in one query -17 was used and the other -18 was used.Tara |
|
|
|
edpel
Starting Member
22 Posts |
Posted - 2003-11-18 : 09:40:20
|
| sorry that one query used 18 and the other 17, just copied in the wrong one. Converting the datetimes to chars or varchars just doesn't work as it returns a date of 1926 and a date of 1988 and they aren't both under 18.Tara,I like your query...should the number be divided by 365.25 for leap year?Thanks,Eddie |
|
|
|
edpel
Starting Member
22 Posts |
Posted - 2003-11-18 : 09:58:56
|
| Thanks for all the suggestions, I actually got the char conversion to work, just had to set the style to 102 so that the year was first.Thanks,Eddie |
|
|
|
|
|
|
|
|
easy question..calculating ages