Select new entries for confirmation email

Question

0.00/5 (No votes)

See more:

Hi all,

There is probably an easier way of achieving what I am trying to do. Please suggest it if you do know.

I would like to select new data from three tables that are only a minute old. Then using C#, send confirmation and reminder email. Here is my script.

SQL

SELECT B.BookingID,
		S.StaffName,
		A.Asset,
		B.StartDate,
		B.EndDate,
		B.StartTime, 
		B.EndTime,
		A.Campus,
		B.Location,
		B.Arrengement,
		B.Messege
FROM tblBooking as B INNER JOIN tblAssets as A 
								 
		on B.AssetID = A.AssetID
		inner join tblStaff as S
		on B.StaffID = S.StaffID
where B.StartTime <= DATE_SUB(curdate() INTERVAL 5 MINUTE)

when executing the above, I get the following;

Msg 195, Level 15, State 10, Line 15
'curdate' is not a recognized built-in function name.

I tried most solution I found online with SSMS giving me different errors. I appreciate it if you're able help.

Posted 20-Apr-15 22:47pm

Awoldeselassie

Updated 20-Apr-15 23:00pm

v3

Add a Solution

Comments

Awoldeselassie 23-Apr-15 4:22am

Solved

Add a new datetime column and set the default to CURRENT_TIMESTAMP

SELECT B.BookingID,
S.StaffName,
A.Asset,
B.StartDate,
B.EndDate,
B.StartTime,
B.EndTime,
A.Campus,
B.Location,
B.Arrengement,
B.Messege
FROM tblBooking as B INNER JOIN tblAssets as A

on B.AssetID = A.AssetID
inner join tblStaff as S
on B.StaffID = S.StaffID
where B.TimeOfBooking >= dateadd(minute, -1, getdate())
and B.TimeOfBooking <= dateadd(minute, -0, getdate())

returns records a minute old.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-04-20T23:00:00

Solution 1

It's quite clear ... whatever version of SQL you are using does not have a function called curdate(). If you are using SQL Server then try using getdate().

You will probably then get a similar error on DATE_SUB - try datediff[^] instead.

The real message here is don't copy solutions online unless you are sure they are for the same language/framework/etc that you need.

[EDIT - response to OP comment to Solution 2]

Quote:
I have tried to resolve it using different function.. and tried researching.
for:
WHERE StartDate <= CONVERT(dateTime, DATEDIFF(minute, 5, GETDATE()))
I get error
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

The Datediff function returns an integer - ie. the number of minutes (or seconds, or years etc etc) and cannot be converted to a DateTime. Use your StartDate and StartTime columns and the current datetime within the function e.g.

where DATEDIFF(minute, GETDATE(), StartDate + CONVERT(DateTime, StartTime)) > 5

Posted 20-Apr-15 23:00pm

CHill60

Updated 21-Apr-15 0:05am

v2

Comments

Awoldeselassie 21-Apr-15 6:12am

thanks for your help. I was using the convert because there is incompatible error without it as datediff is an int. I am no expert at this clearly, so please overlook my silly mistakes. I will try your solution now.

CHill60 21-Apr-15 6:13am

Give me a shout if you still have problems

Awoldeselassie 21-Apr-15 6:19am

with your solution, it returns all records with StartDate <= today with < 5, or all records >= today with > 5, which is what I was able to do before.

however, I would like the query to return records that are entered in table within the last 5 minutes only. Or if you know a simpler way of sending booking confirmation email, I would appreciate it.

CHill60 21-Apr-15 6:56am

That where-statement in my solution only deals with minutes not days - are you sure you've entered it correctly? Do you have StartDate as Date and StartTime as Time on your database?
Personally, I would send the confirmation email at the time I was making the booking

Awoldeselassie 21-Apr-15 7:28am

I copied and entered your solution. Your script deals with minutes, but returns anything that has a StartTime of greater than GETDATE() + 5 minutes or less than GETDATE() - 5 minutes. So that is all records.

Yes you are right, the emails are sent at the time of booking. But I need a trigger that fires looking for new bookings in order to include them in the email. Trigger fires every two minutes looking for records that are only one minute old, then send email to all new records.

three types of email are included in the system:
1, send email to admin every morning with the days booking
WHERE StartDate = CONVERT(DateTime, DATEDIFF(DAY, 0, GETDATE()))
2, send reminder email to whoever responsible to the delivery before 30 min
where B.StartTime < convert (time, dateadd(minute, -30, getdate() ))
and B.StartTime > convert(time, dateadd(minute, -31, getdate()) )
and B.StartDate = CONVERT(DateTime, DATEDIFF(DAY, 0, GETDATE()))
3, send confirmation email to user immediately after booking
????

CHill60 21-Apr-15 10:39am

No - my solution does not do greater than or less than. It does return anything older than 5 minutes though. Try where DATEDIFF(minute, StartDate + CONVERT(DateTime, StartTime), GETDATE())<=5 instead.
I still think you should send the confirmation email immediately after booking as part of the process of booking. You should also record the datetime the email was sent. Consider what happens if there is a system outage (planned or otherwise) - you may end up with bookings for which no email has been sent. Personally I wouldn't separate date and time into two separate fields either - it's a waste of space when a simple datetime column provides the same information.

Abhinav S · Answer 2 · 2015-04-20T23:00:00

Solution 2

CURDATE is a mySql function. Are you using mySql?
If its SQL Server you need to use Getdate().

Posted 20-Apr-15 23:00pm

Abhinav S

Comments

Awoldeselassie 21-Apr-15 5:12am

I have tried to resolve it using different function.. and tried researching.
for:
WHERE StartDate <= CONVERT(dateTime, DATEDIFF(minute, 5, GETDATE()))
I get error
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

CHill60 21-Apr-15 5:33am

~~Why the CONVERT?~~ I've updated my solution with the reason why you get this latest error