MSSQL Date Function! Creating Function 1st Time...
In this function im checking date, if web form submitted date less then current date and start date with End date.
please find function code below!
please help me how to write in storedprocedure.
please find function code below!
please help me how to write in storedprocedure.
CREATE FUNCTION myDateCompare(@startDate DateTime, @EndDate DateTime)
RETURNS @resultDate
AS
BEGIN
WITH datesFunc(date1, date2) AS
(
SELECT CAST(date1 AS DATETIME),
CAST(date2 AS DATETIME)
)
SELECT CASE WHEN CAST(@startDate AS DATE) <= CAST(@EndDate AS DATE) THEN 'Start Date Cannot Start Before End Date' ELSE
SELECT CASE WHEN CAST(@startDate AS DATE) <= CAST(getDate AS DATE) THEN 'Start date cannot be less than current date' ELSE 'Y' END
FROM datesFunc
RETURN;
END;ASKER
before inserting in database i just want to use this function so less code on application side
if my function send any error date, for Example: if date is already passed or start date greater then End date then it will not perform inserting command. send message to application.
if my function send any error date, for Example: if date is already passed or start date greater then End date then it will not perform inserting command. send message to application.
USE [Registration]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ADDEvent]
@EventType varchar(50),
@EventNumber varchar(50),
@EventTitle1 varchar(50),
@EventTitle2 varchar(50),
@EventBase varchar(50),
@EventStartDt varchar(50),
@EventEndDt varchar(50),
@Status varchar(50) OUTPUT
AS
BEGIN
insert into tbl_EventCategory
(eventType,eventNumber,eventTitle1,eventTitle2,eventBase,eventStartDt, eventEndDt)
values
(@EventType,@EventNumber,@EventTitle1,@EventTitle2,@EventBase,@EventStartDt,@EventEndDt)
END
In that case just call your function from within your SP - that avoids duplicating code.
So something like
So something like
if (select dbo.myDateCompare(@EventStartDt, @EventEndDt)) = 'Y' begin
insert into ...
endASKER
that is right but my main issue is my function script giving error can u have a look my function script ... if you can help me ...
Thanks ...
Thanks ...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As an SP do you want to return the result as a parameter as a result set or as the return value?