Friday, December 19, 2008

How to check the Date Format Using SQL SERVER 2005

Hello ...
Today , I am presenting a very Good Stored procedure as well as the UseDefined funtion and its use in the stored procedure the date formate is MM/DD/YYYY.
-- The Procedure is Used to get the Data For Event start date,Begin Time and End Time.
CREATE proc [dbo].[usp_GetEventDateTime]
@eventId uniqueidentifier
--The EventId corresponds to which we have to get data
Select eventDateTimeId, eventDate, beginTime, endTime
,dbo.udf_IsValidEventStartTimeFormat(beginTime) As 'StartTimeFormat', dbo.udf_IsValidEventEndTimeFormat(endTime) As 'EndTimeFormat'
From EventDateTime
Where eventId = @eventId
--dbo.udf_IsValidEventStartTimeFormat is used to get the correct format of the Time
The UserDefined Functio IsValidEventStartTimeFormatn Is Geven As Given Below:

CREATE FUNCTION [dbo].[udf_IsValidEventStartTimeFormat] (@beginTime varchar(50))
RETURNS smallint
Declare @ReturnVal smallint
Set @ReturnVal = 1
If @beginTime = '' Or @beginTime IS NULL Or
CharIndex(':',@beginTime) <> 3 Or
CharIndex(' ',@beginTime) <> 6 Or
Len(SubString(@beginTime,CharIndex(' ',@beginTime)+1,Len(@beginTime))) != 2 Or
SubString(@beginTime,CharIndex(':',@beginTime)+1,1) = ' ' Or
isnumeric(substring(@beginTime,0,CharIndex(':',@beginTime))) !=1 Or
isnumeric(SubString(@beginTime,CharIndex(':',@beginTime)+1, CharIndex(' ',@beginTime) - (CharIndex(':',@beginTime)+1) ) ) !=1
Set @ReturnVal = 0 -- If not valid time format (HH:MM AM/PM)
RETURN @ReturnVal
With Love..
Sanjeev Chauahn
HelpOnDesk Team

No comments: