IN my application i am using vb.net and sql server 2008.my application hosted in US server so it takes time from US server.Users us application in INDIA they want to change time from US to INDIA.the change is in start time and END time in stored procedure as follows.plz provide me help
CREATE PROCEDURE [dbo].[usp_production report]
@fromdate datetime,
@todate datetime,
@Process varchar(10),
@intSearch char(1)
AS
Begin
IF @Process='a' and @intSearch='1'
Begin
SELECT
Convert(Varchar,[Receipt Date],101) AS [Receipt Date]
,[Process]
,[UIN]
,[Image No.]
,[Image Page Count]
,[User Name]
,Convert(varchar,[End Time],101) AS [Production Date],CONVERT(VARCHAR(8)
, [Start Time] , 108) AS [Start Time],CONVERT(VARCHAR(8)
, [End Time] , 108) AS [End Time] ,[Time Taken]
,[Vendor Name],[Vendor ID],[Address ID]
,[Contact Employee],[Timekeeper ID]
,[Invoice No],[Voucher No],replace(CONVERT(VARCHAR(8) , [Invoice Date] , 10),'-','') AS [Invoice Date]
,[Total Amount],[Currency],[Taxes]
,[State],[PO Number]
,CASE [Status]
WHEN 0 THEN 'UnAllocated'
WHEN 1 THEN 'Allocated/Ready'
WHEN 2 THEN 'InProcess'
WHEN 21 THEN 'Query Raised'
WHEN 3 THEN 'Completed'
ELSE
'UNKNOWN'
END AS [Status]
,[Reason Code],[Notes]
,[Production Counter]
FROM
vw_s_View_Production_Report
WHERE
( [Receipt Date]>=@fromdate and [Receipt Date]<=@todate) order by [Receipt Date]
End
Else if @Process='b' and @intSearch='1'
Begin
SELECT
Convert(Varchar,[Receipt Date],101) AS [Receipt Date]
,[Process]
,[UIN]
,[Image No.]
,[Image Page Count]
,[User Name]
,Convert(varchar,[End Time],101) AS [Production Date],CONVERT(VARCHAR(8) , [Start Time] , 108) AS [Start Time],CONVERT(VARCHAR(8) , [End Time] , 108) AS [End Time],[Time Taken]
,[Vendor Name],[Vendor ID],[Address ID]
,[Contact Employee],[Timekeeper ID]
,[Invoice No],[Voucher No],replace(CONVERT(VARCHAR(8) , [Invoice Date] , 10),'-','') AS [Invoice Date]
,[Total Amount],[Currency],[Taxes]
,[State],[PO Number]
,CASE [Status]
WHEN 0 THEN 'UnAllocated'
WHEN 1 THEN 'Allocated/Ready'
WHEN 2 THEN 'InProcess'
WHEN 21 THEN 'Query Raised'
WHEN 3 THEN 'Completed'
ELSE
'UNKNOWN'
END AS [Status]
,[Reason Code],[Notes]
,[Production Counter]
FROM
vw_DEQC_View_Production_Report
WHERE
( [Receipt Date]>=@fromdate and [Receipt Date]<=@todate and Process=@Process) order by [Receipt Date]
End
End
[edit]Code block added - OriginalGriff[/edit]