Archive for the 'sql' Category

March 19
east coast time
posted by mike at 1:26 pm

so had to do something interesting for work that I thought would be usefull for others... 2 servers on each coast... need to write the same time, but code does not know which server its running on... so I use gmt to figure out the right time....

heres the solution in sql and c#... sql is useful , as that you could set the default of a datetime field in sql to dbo.fn_GetEastCoastTime(getdate().getutc())

PLAIN TEXT
SQL:
  1. CREATE FUNCTION [dbo].[fn_GetEastCoastTime]
  2. (@date datetime,@utcdate datetime
  3. )
  4. RETURNS datetime
  5. AS
  6. BEGIN
  7.  
  8. declare  @march datetime
  9. declare  @november datetime
  10. declare  @dstStart datetime
  11. declare  @dstEnd datetime
  12. declare  @difamt int
  13.  
  14. SET @march= '3/01/'+cast(year(@date)AS varchar)
  15. SET @november= '11/01/'+cast(year(@date)AS varchar)
  16.  
  17. SET @dstStart = DATEADD(wk,1,(@march)+(7-DATEPART(dw,(@march-
  18. Day(@march)))))
  19.  
  20. SET @dstEnd = DATEADD(wk,0,(@november)+(7-DATEPART(dw,(@november-
  21. Day(@november)))))
  22.  
  23. IF(@date> @dstStart AND @date <@dstEnd)
  24. SET @difamt = -4
  25. else
  26. SET @difamt = -5
  27.  
  28. RETURN dateadd(hour,datediff(hour,dateadd(hour,@difamt,@utcdate),@date),@date)
  29.  
  30. END

PLAIN TEXT
C#:
  1. public static DateTime EastCoastDate
  2.  
  3. {
  4.  
  5. get
  6.  
  7. {
  8.  
  9. int intTimeShift =-5;
  10.  
  11. if(TimeZone.CurrentTimeZone.IsDaylightSavingTime(System.DateTime.Now))
  12.  
  13. intTimeShift = -4;
  14.  
  15. TimeSpan hourdiff =  System.DateTime.UtcNow.AddHours(intTimeShift) - System.DateTime.Now;
  16.  
  17. return System.DateTime.Now.AddHours(hourdiff.Hours);
  18.  
  19. }
  20.  
  21. }

Comments Off || Topcs: geek, sql, c#, .net