Convert date to UTC date

• Resender

  SSCertifiable

  Points: 7443

  More actions

  March 31, 2010 at 6:45 am

  #220738

  Hello

  I already ran trough the forums but found no satifiable answers.

  I work for a logistical firm and were using a series of interfaces (programmed in VB6) to

  check & process data coming from our clients. (The interfaces are part of a program that reasonably new).

  When we get an order we have to check whether the lot in the files corresponds with a lot in the warehouse, we can check on the following values lot,lottable03,lottable05,sku,storer

  lot <=Is the lot we want

  lottable03<=Description of what kind of good it is.

  lottable05<=Expiry Date, this is the date the good has to be gone out of the warehouse & or sold.

  sku<=The item number, which is unique per storer.

  storer<=A value to identify the firm that supplies the goods

  lottable05 is converted to UTC before being stored in the database, a function provided by the forehand mentioned program allows this. This results in the following value being stored YYYY-DD-MM 22:00:00. We don't get the hours from our client so its stored as 10PM the day before.

  Now comes the stinger since last Sunday when DST started this method started converting to YYYY-DD-MM 23:00:00, since the same function is used to convert the date for the checks this results in lots not found that were inputted last week before DTS.

  So I like to know if i could make the conversion in SQL.

  Thank you for reading this & taking your time giving me an answer.

  If I wasn't clear on something or more explanation is required please ask.

• Hunterwood

  SSCrazy

  Points: 2074

  More actions

  March 31, 2010 at 7:06 am

  #1143052

  Hi,

  To convert a datetime value to the format YYYY-MM-DD hh:mm:ss you write:

  declare @myDate datetime

  set @myDate = getdate()

  -- Convert date to string with format YYYY-MM-DD hh:mm:ss

  select CONVERT(char(19), @myDate, 120)

  Since you had some problem with the hours being wrong, you could use DATEADD to change the hours:

  -- Set the hour-part of the date to 00

  select DATEADD(hh, -DATEPART(hh, @myDate), @myDate)

  I hope this answers (some of) your question(s)...

  /Markus

• Beedle

  SSC Enthusiast

  Points: 150

  More actions

  March 31, 2010 at 12:05 pm

  #1143321

  Here's a function I wrote to convert from central time to GMT (CST or CDT to UTC in other words) - might help:

  Create FUNCTION [dbo].[udfConvertToGMT]

  (@InputDate datetime)

  RETURNS datetime

  AS

  /*

  Script Date: 08/11/2009

  Author: Beedle

  Purpose: Converts from CST (or CDT if date is during daylight savings time) to GMT

  */

  --DECLARE @InputDate datetime

  --Select @InputDate = '10/4/1985 08:00:00'

  BEGIN

  DECLARE @converted_date datetime

  DECLARE @DST_Start datetime

  DECLARE @DST_End datetime

  DECLARE @GMT_Offset_Destination int

  -- Get start and end dates for daylight savingstime for the year in question

  Select@DST_Start =Case when datepart(year,@InputDate) >= 2007 then

  '3/1/'+convert(varchar,DATEPART(year, @InputDate))

  when datepart(year,@InputDate) between 1986 and 2006 then

  '4/1/'+convert(varchar,DATEPART(year, @InputDate))

  Else -- Year prior to 1986

  '4/30/'+convert(varchar,DATEPART(year, @InputDate))

  End,

  @DST_End =Case when datepart(year,@InputDate) >= 2007 then

  '11/1/'+convert(varchar,DATEPART(year, @InputDate))

  Else

  '10/31/'+convert(varchar,DATEPART(year, @InputDate))

  End

  Select@DST_Start =

  --2007 till present get second Sunday of March, 1986-2006 get first Sunday of April, prior to 1986 get last sunday in April

  Case when datepart(year,@InputDate) >= 2007 then

  dateadd(hour,8,dateadd(day,

  ((ceiling((cast(17 as decimal(8,2)) % datepart(dw,@DST_Start))/10) * 7) + 8) - datepart(dw,@DST_Start),

  @DST_Start))

  When datepart(year,@InputDate) between 1986 and 2006 then

  dateadd(hour,8,dateadd(day,

  ((ceiling((cast(17 as decimal(8,2)) % datepart(dw,@DST_Start))/10) * 7) + 1) - datepart(dw,@DST_Start),

  @DST_Start))

  Else -- Year prior to 1986

  dateadd(hour,8,dateadd(day,

  0-(datepart(dw,@DST_Start)-1),

  @DST_Start))

  End,

  --2007 till present get first Sunday of November, else get last Sunday of October

  @DST_End =

  Case when datepart(year,@InputDate) >= 2007 then

  dateadd(second, -1,dateadd(hour,8,dateadd(day,

  ((ceiling((cast(17 as decimal(8,2)) % datepart(dw,@DST_End))/10) * 7) + 1) - datepart(dw,@DST_End),

  @DST_End)))

  Else

  dateadd(second, -1,dateadd(hour,8,dateadd(day,

  0-(datepart(dw,@DST_End)-1),

  @DST_End)))

  End,

  @GMT_Offset_Destination = 360

  RETURN Case when @InputDate BETWEEN @DST_Start AND @DST_End then

  DATEADD(MINUTE, @GMT_Offset_Destination - 60, @InputDate)

  Else

  DATEADD(MINUTE, @GMT_Offset_Destination, @InputDate)

  End

  END