Solved

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

Posted on 2013-12-21
7
569 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
Independent Software Vendors: 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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

749 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