?
Solved

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

Posted on 2013-12-21
7
Medium Priority
?
584 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 668 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 664 total points
ID: 39734489
RETURNS @resultDate

should be

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

Accepted Solution

by:
ste5an earned 668 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 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