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 |
|
chipembele
Posting Yak Master
106 Posts |
 Posted - 2007-06-22 : 11:04:03
|
| HiI have a query that a user runs and it includes this part.CAST(CASE WHEN GetDate() > DateAdd(d, 13, DDOfferLetter.[PrintedOn]) AND GetDate() < DateAdd(d, 21, DDOfferLetter.[PrintedOn])AND A.DecisionID IS NULL AND A.OfferId = 2 THEN 1 ELSE 0 END AS BIT) AS RequireASecondOfferLetter The problem im having is that i get diffeent answers depending on what time of day the query is run. This is because the GetDate() and the DDOfferLetter.[PrintedON] include the time as well as the date. I've tried CONVERT and fiddled around with it for 4 hours now.Can someone give me some guidance please?ThanksDan |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-22 : 11:11:43
|
| What do you want?maybe something likedatediff(dd,GetDate()+13, DDOfferLetter.[PrintedOn]) >= 0 AND datediff(dd,DDOfferLetter.[PrintedOn, GetDate()+21,]) >= 0)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-22 : 11:12:58
|
| change getdate() to SELECT DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
chipembele
Posting Yak Master
106 Posts |
Posted - 2007-06-22 : 13:35:34
|
| Thanks for your replies. I should have been clearer in my request.This is only the part of the query that i'm having trouble with. What the query does is this. When todays date (GetDate() from the SQL server) is more than 13 days and less than 21 days since a letter was printed (based on the DDOffer Letter.[printedon] Field) and the decisionid field is NULL and the OfferId is 2 then input a 1 in the RequireASecondOffer field. We take a 1 to mean TRUE in this instance.I'm not at my work PC now to test your answers but does this seem like they will solve the problem?ThanksDan |
|
|
|
|
|
|
|
|
GetDate() and CONVERT