Solved

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

Posted on 2013-12-21
7
567 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 69

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 33

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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