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 |
|
Gary Costigan
Yak Posting Veteran
95 Posts |
 Posted - 2003-10-02 : 17:46:26
|
| Need a little help with the GETDATE () function.I am currently using the following code to search my table and show occurrences that happened yesterday in the "WHERE" area of my query.and DATEPART (mm,losttime.CTwentyOne.AbsenceStartDate) = DATEPART (mm,GETDATE())and DATEPART (dd,losttime.CTwentyOne.AbsenceStartDate) = DATEPART (dd,(DATEADD(dd, -1, GETDATE())))and DATEPART (yy,losttime.CTwentyOne.AbsenceStartDate) = DATEPART (yy,GETDATE())This gives me the results I need but I'm sure there is an easier way of doing it.I played with :and losttime.CTwentyOne.AbsenceStartDate = DATEADD(dd, -1, GETDATE())But nothing is returned as a match.Any suggestions?Thanks as always.GC |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-02 : 17:51:51
|
| [code]CONVERT(VARCHAR(50), losttime.CTwentyOne.AbsenceStartDate, 101) = DATEADD(dd, -1, CONVERT(VARCHAR(50), GETDATE(), 101))[/code]Style 101 removes the time part of datetime.Tara |
 |
|
|
Gary Costigan
Yak Posting Veteran
95 Posts |
Posted - 2003-10-03 : 16:57:19
|
| Tara,I tried it, but the query came back without a match where I get 73 names with the other code. Sorry.Thanks for your help though!!!GC |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-03 : 17:01:23
|
| Run this to see if what format it is in:SELECT CONVERT(VARCHAR(50), losttime.CTwentyOne.AbsenceStartDate, 101)FROM WhateverTableItIsSELECT DATEADD(dd, -1, CONVERT(VARCHAR(50), GETDATE(), 101))FROM WhateverTableItIsBy comparing them, you should see where the problem is. If you want, post what the data looks like in the above two queries, and we'll fix whatever bug that I have in the code.Tara |
|
|
|
|
|
|
|
|
GETDATE ()