Click here to Skip to main content
15,888,144 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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



SQL
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]
Posted
Updated 8-Jun-13 1:32am
v2

Don't.

Leave the data in the Database well alone, and store it in UTC (or US if you must, as long as you pick a single unified time base you will be ok)
Any change of time should be done immediately data is received from the client and converted to internal form (preferably UTC) while you know what zone the user is (and thus the basis for the entered time). You then convert it to a user specific time for output as late as possible - immediately before presentation.

Don't even think about localizing times in your DB: it will just end up in tears unless you always know what locale the data came from, and what locale it is going to.
 
Share this answer
 
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900