Date Issue

[chinedu]

Hi everyone,
I am sending reminder emails and would like the email sent out 3 days from the submit date.
For instance, if a customer order was sent out today, May 13, 2004, We would like to send the customer an email reminder on Sunday May 16, 2004 to remind them that their order was sent out 3 days ago.

The problem that I have with the current code that I have is that it is sending out everything that that is greater than 3 days.
We don't want to send out anything that is earlier than 3 days or longer than 3 days.

Please see if you can help out on this.
This is currently what I have:

SELECT Date_Submitted
FROM dateTable
WHERE DateAdd(hour, -72, getdate()) > Date_Submitted

 

[JayKusch]

Try this ...

SELECT CONVERT(Char,Date_Submitted,101)
FROM dateTable
WHERE CONVERT(Char,DateAdd(day, -3, getdate()),101) > CONVERT(Char,Date_Submitted,101)


Thanks

J. Kusch

 

[chinedu]

Thanks for the response.
It isn't working.

Example, only dates submitted 05/05/2004 or earlier were retrieved.
There are a bunch of dates I submitted today that are being retrieved.

I probably didn't explain myself well.
If an order is submitted by a client, we would want that order to be sent out only after the 3rd day since it was submitted, not before the 3rd day and not after the 3rd day.
Basically, it is a one reminder type deal.
If they act on it after that one reminder, great, if not, great; that is the spec.

Thanks again for the response

 

[JayKusch]

SELECT CONVERT(Char,Date_Submitted,101)
FROM dateTable
WHERE CONVERT(Char,DateAdd(day,-3,getdate()),101) =
CONVERT(Char,Date_Submitted,101)


Thanks

J. Kusch

 

[chinedu]

Now, nothing is getting returned.
I have dates like these:
05/08/2004, 05/09/2004, 05/10/2004 and 05/11/2004.

I am expecting to return either 05/09/2004 or 05/10/2004.
Nothing is getting retrieved.
Again, I appreciate the help.

 

[tektipdjango]

I assume if you're on May 13th, you want the records from May 10th 00:00:00 to May 10th 23:59:59

So try :

declare @startdate datetime
declare @endDate datetime


set @startDate= convert(datetime,convert(char,getdate(),101),101)-3
set @endDate=@startDate+1

SELECT ...,Date_Submitted
FROM dateTable
WHERE (Date_Submitted >= @startDate and Date_Submitted < endDate)

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[JayKusch]

For the records on the 10th run this ...

SELECT CONVERT(Char,Date_Submitted,101)
FROM dateTable
WHERE CONVERT(Char,DateAdd(day,-3,getdate()),101) =
CONVERT(Char,Date_Submitted,101)

For records on the 9th run this ...

SELECT CONVERT(Char,Date_Submitted,101)
FROM dateTable
WHERE CONVERT(Char,DateAdd(day,-4,getdate()),101) =
CONVERT(Char,Date_Submitted,101)

For records for BOTH the 9th and 10th

SELECT CONVERT(Char,Date_Submitted,101)
FROM dateTable
WHERE CONVERT(Char,DateAdd(day,-3,getdate()),101) >=
CONVERT(Char,Date_Submitted,101)
AND
CONVERT(Char,DateAdd(day,-4,getdate()),101) <=
CONVERT(Char,Date_Submitted,101)



Thanks

J. Kusch

 

[chinedu]

hi,
I am assuming you meant @endDate with this syntax

WHERE (Date_Submitted >= @startDate and Date_Submitted < endDate)

For some reason, no records are being returned.

 

[chinedu]

This column called Date_Submitted is a datetime field.
Do I need to convert it to character field because nothing I have tried so far from you guys is working and I know these are good codes.

 

[JayKusch]

I created a table w/ a datetime field called Date_Submitted.

I entered several records for the days of the 8th thu 13th(today)

The code below ran and returned exactly what it was suppose to. Are you sure you are running this code against the correct table?

===============================================

For the records on the 10th run this ...

SELECT CONVERT(Char,Date_Submitted,101)
FROM dateTable
WHERE CONVERT(Char,DateAdd(day,-3,getdate()),101) =
CONVERT(Char,Date_Submitted,101)

For records on the 9th run this ...

SELECT CONVERT(Char,Date_Submitted,101)
FROM dateTable
WHERE CONVERT(Char,DateAdd(day,-4,getdate()),101) =
CONVERT(Char,Date_Submitted,101)

For records for BOTH the 9th and 10th

SELECT CONVERT(Char,Date_Submitted,101)
FROM dateTable
WHERE CONVERT(Char,DateAdd(day,-3,getdate()),101) >=
CONVERT(Char,Date_Submitted,101)
AND
CONVERT(Char,DateAdd(day,-4,getdate()),101) <=
CONVERT(Char,Date_Submitted,101)



Thanks

J. Kusch

 

[chinedu]

It is working now.
Thanks for all the help.
I don't know why it wasn't working yesterday.

One last question, please?

Is it possible to highlight a text with stored proc?
For instance,
If I have a text on my stored proc that says:
Hello, when are you coming?, and I want to highlight the word Hello, is it possible with stored proc?

Again, many thanks for all the help.

 

[JayKusch]

I do not believe that is possible. So ... what solution above worked? We would all like to know in case others run across a similar issue as you have.

Have a great day!

Thanks

J. Kusch

 

[chinedu]

That's what I thought too but I needed expert opinion.

As for what worked, I believe all of the options you listed would have worked but the one I am interested in is this:

For the records on the 10th run this ...

SELECT CONVERT(Char,Date_Submitted,101)
FROM dateTable
WHERE CONVERT(Char,DateAdd(day,-3,getdate()),101) =
CONVERT(Char,Date_Submitted,101)

since we want any others that were processed 3 days to be retrieved.

 

[JayKusch]

Great!

Thanks

J. Kusch