Solved

MSSQL Date Function! Creating Function 1st Time...

Posted on 2013-12-21
7
577 Views
Last Modified: 2013-12-25
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.


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;

Open in new window

0
Comment
Question by:xeondxb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39734319
What do you want it as a SP? Surely a function is more practical?

As an SP do you want to return the result as a parameter as a result set or as the return value?
0
 

Author Comment

by:xeondxb
ID: 39734325
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.

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

Open in new window

0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39734338
In that case just call your function from within your SP - that avoids duplicating code.

So something like

if (select dbo.myDateCompare(@EventStartDt, @EventEndDt)) = 'Y' begin
  insert into ...
end

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:xeondxb
ID: 39734351
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 ...
0
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 167 total points
ID: 39734423
If all you want to get is the result of the check:
CREATE FUNCTION myDateCompare(@startDate DateTime, @EndDate DateTime)
RETURNS @resultDate 
AS
BEGIN
  return case
     when @startDate <= @EndDate then 'Start Date Cannot Start Before End Date' 
     when @startDate <= getdate() then 'Start date cannot be less than current date'
     else 'Y'
     end
END

Open in new window

0
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 166 total points
ID: 39734489
RETURNS @resultDate

should be

RETURNS varchar(1024) -- Or whatever type you are trying to return.
0
 
LVL 34

Accepted Solution

by:
ste5an earned 167 total points
ID: 39734576
Instead of using a function in T-SQL you should use

1. CHECK CONSTRAINT


Use a strict model on database level.
CREATE TABLE [dbo].[ADDEvent]
(
	EventType VARCHAR(50),
	EventNumber VARCHAR(50),
	EventTitle1 VARCHAR(50),
	EventTitle2 VARCHAR(50),
	EventBase VARCHAR(50),
	EventStartDt DATE NOT NULL,
	EventEndDt DATE NOT NULL,
	CONSTRAINT CK_dbo_ADDEvent_StartEndDtRange CHECK ( EventStartDt <= EventEndDt )
);

Open in new window

btw, post your table DDL. The use of VARCHAR's in the SP makes no sense.

2. Input Validation


To avoid unnecessary database round-trips, validate the entered information also in your upper, front-end layers.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question