Link to home
Create AccountLog in
Avatar of martgriff
martgriff

asked on

SQL Case Expression

I have a Case expression as seen below, it inserts the data in to a table on the SP.
i want to add the date and time to the case at the time of the insert how do i do this e.g.
'This is a message from xxxxxxx to remind you that our engineer will be calling tomorrow Morning to attend to your gas repair. Sent at: 01/03/2008 07:00:00'
SELECT			tbl_property.CONTACT, 
					case when tbl_repair_order.BOOKEDTIME = 'AM' 
						then 'This is a message from xxxxxxx to remind you that our engineer will be calling tomorrow Morning to attend to your gas repair.'
					when tbl_repair_order.BOOKEDTIME = 'PM' 
						then 'This is a message from xxxxxxx to remind you that our engineer will be calling tomorrow Afternoon to attend to your gas repair.'
					when tbl_repair_order.BOOKEDTIME = 'AT' 
						then 'This is a message from xxxxxxxx to remind you that our engineer will be calling tomorrow to attend to your gas repair.'
					when tbl_repair_order.BOOKEDTIME = 'FC' 
						then 'This is a message from xxxxxxx to remind you that our engineer will be calling tomorrow Morning to attend to your gas repair.'
					End as SMSMessage,
					
					Convert(Varchar,getdate(),101) + ' ' + '19:00:00',
					tbl_repair_order.CONTRACT + ' - ' + Convert(Varchar,tbl_repair_Order.ORDERID)
	   
	FROM			tbl_contract RIGHT OUTER JOIN
					tbl_repair_order ON tbl_contract.CONTRACT = tbl_repair_order.CONTRACT LEFT OUTER JOIN
					tbl_property ON tbl_repair_order.PROPREF = tbl_property.PROPREF
		
	WHERE			BookedDate = CONVERT(CHAR(10),dateadd(D,+1,getdate()),101) and tbl_contract.SMSEnabled = 1 and Len(Contact) = 11 and ISNULL(SMSSent, 0) <> 1 and sendsms = 1

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of pollock_d
pollock_d

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account