Through front end i am passing date time as “dd/MM/yyyy” format
txtFromDate.Text =
DateTime.Today.ToString(“dd/MM/yyyy”, DateTimeFormatInfo.InvariantInfo);
CREATE
PROCEDURE [dbo].Dmn_SuccessStoryReport –’Bangalore’,’05/01/2009′,’16/01/2009′,’All’,1,5(
@city
@FromDate
@ToDate
@status
VARCHAR(200) = NULL, VARCHAR(10) = NULL, VARCHAR(10) = NULL, VARCHAR(5) = NULL,@PageNo
@PageSize
SMALLINT, SMALLINT )
AS
SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN
DECLARE @StartRow SMALLINT DECLARE
@EndRow SMALLINT SET
@EndRow = @PageNo * @PageSize SET
@StartRow = (@EndRow) -(@PageSize -1) DECLARE
DECLARE
SELECT
@StartDate datetime @EndDate datetime @StartDate = CONVERT(DATETIME, @FromDate, 103)SELECT
@EndDate = CONVERT(DATETIME, @ToDate, 103)DECLARE
@all AS VARCHAR (40) SET @all = ‘ALL’ DECLARE
ROWNO
SS_ID
LOCATION_ID
COMPANY_NAME
LOCALITY
CITY
@tablevariable TABLE ( INT IDENTITY(1, 1) PRIMARY KEY, INT, INT, VARCHAR(500), VARCHAR(200), VARCHAR(200),STATE
[User_Name]
CREATED_DATE
STATUS
APPROVED_BY
VARCHAR(200), VARCHAR(400), DATETIME, VARCHAR(20), VARCHAR(60) ) INSERT INTO @tablevariable(SS_ID
LOCATION_ID
COMPANY_NAME
LOCALITY
CITY
, , , , ,STATE
[User_Name]
CREATED_DATE
STATUS
APPROVED_BY
, , , , )
SELECT S.SS_ID,S
.LOCATION_ID,M
.COMPANY_NAME,L
.LOCALITY,L
.CITY,L.STATE,S
.[User_Name],S
.CREATED_DATE,S
.STATUS,S
DMSMASTER M
.APPROVED_BY FROM (NOLOCK),DMSLOCATION L
(NOLOCK),SUCCESSSTORY S
M
(NOLOCK) WHERE .MASTER_ID=L.MASTER_ID AND L.LOCATION_ID=S.LOCATION_ID AND (S.City =@city OR @city is null)
AND (S.Status =@status or @status = @all)
– and CAST(FLOOR( CAST( S.CREATED_DATE AS FLOAT ) )
–AS DATETIME–Try casting to float, rounding, and back to date
–)
and [dbo].ConverttofloatRound(S.CREATED_DATE)–Try casting to float, rounding, and back to date
[LOCATION_ID]
[COMPANY_NAME]
[LOCALITY]
[CITY]
[STATE]
[User_Name]
CREATED_DATE
STATUS
APPROVED_BY
[ROWNO]
between @StartDate AND @EndDateORDER BY [COMPANY_NAME]SELECT SS_ID, , , , , , , , , FROM @tablevariable WHERE BETWEEN @StartRow AND @EndRowORDER BY [COMPANY_NAME]END
FUNCTION:
– Description : This function is used for Try casting to float, rounding, and back to date
–ex: 2010-01-21 15:07:20.827 convert into 2010-01-21 00:00:00.000
—————————————————————————–
ALTER
FUNCTION ConverttofloatRound(@createdDate DATETIME)
RETURNS
DATETIMEAS
BEGIN
RETURN CAST(FLOOR( CAST(@createdDate AS FLOAT ) )
AS
DATETIME)
END
