Display CET Time

• Autumnstorms

  SSC Veteran

  Points: 220

  More actions

  September 6, 2012 at 2:07 am

  #261145

  Hi folks

  I am pretty new to SQL & SQL Server and i'm having some trouble converting some date and time stored in our database...

  We have a ticket application which stores the date/time when a ticket for an issue is created and solved.

  Unfortunately, it stores this in UTC instead of CET.

  My question is now:

  How can i do a select which automatically displays this time in CET instead of UTC?

  Here is a piece of the SELECT statement i used so far:

  convert (VARCHAR,t.CreatedDateTime, 113) AS [OPENED]

  , convert (VARCHAR,tsc.ChangeDate, 113) AS [CLOSED]

  , datediff (MINUTE,t.CreatedDateTime, tsc.ChangeDate) AS [SOLVING TIME IN MINUTES]

  , datediff (HOUR,t.CreatedDateTime, tsc.ChangeDate) AS [SOLVING TIME IN HOURS]

  , datediff (DAY,t.CreatedDateTime, tsc.ChangeDate) AS [SOLVING TIME IN DAYS]

  BTW... is there a way to convert the DATEDIFF so that it displays how long it took between the two dates in ONE column? (Like 1d 20h 34min.)

  Thanks in advance for the help...

  Regards

  Stef

• ryan.mcatee

  SSC Eights!

  Points: 860

  More actions

  September 6, 2012 at 7:38 am

  #1534456

  Autumnstorms (9/6/2012)

  ---

  Hi folks

  I am pretty new to SQL & SQL Server and i'm having some trouble converting some date and time stored in our database...

  We have a ticket application which stores the date/time when a ticket for an issue is created and solved.

  Unfortunately, it stores this in UTC instead of CET.

  My question is now:

  How can i do a select which automatically displays this time in CET instead of UTC?

  Here is a piece of the SELECT statement i used so far:

  convert (VARCHAR,t.CreatedDateTime, 113) AS [OPENED]

  , convert (VARCHAR,tsc.ChangeDate, 113) AS [CLOSED]

  , datediff (MINUTE,t.CreatedDateTime, tsc.ChangeDate) AS [SOLVING TIME IN MINUTES]

  , datediff (HOUR,t.CreatedDateTime, tsc.ChangeDate) AS [SOLVING TIME IN HOURS]

  , datediff (DAY,t.CreatedDateTime, tsc.ChangeDate) AS [SOLVING TIME IN DAYS]

  BTW... is there a way to convert the DATEDIFF so that it displays how long it took between the two dates in ONE column? (Like 1d 20h 34min.)

  Thanks in advance for the help...

  Regards

  Stef

  One option would be to create a scalar function. Scalar just means a function that returns one value for a set of parameters.

  For instance: dbo.GetLocalTime(@ticketDate, @timeZoneFrom, @timeZoneTo)

  So instead of:

  SELECT convert (VARCHAR,t.CreatedDateTime, 113)...

  You would have:

  SELECT convert(VARCHAR, dbo.GetLocalTime(t.CreatedDateTime, 'UTC', 'CET'), 113)...

  Fortunately, it looks like somebody has already written this function:

  http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/337b0ac4-2a35-4a42-ba08-6e84b18b38a0/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 6, 2012 at 8:50 am

  #1534530

  ryan.mcatee (9/6/2012)

  ---

  Autumnstorms (9/6/2012)

  ---

  Hi folks

  I am pretty new to SQL & SQL Server and i'm having some trouble converting some date and time stored in our database...

  We have a ticket application which stores the date/time when a ticket for an issue is created and solved.

  Unfortunately, it stores this in UTC instead of CET.

  My question is now:

  How can i do a select which automatically displays this time in CET instead of UTC?

  Here is a piece of the SELECT statement i used so far:

  convert (VARCHAR,t.CreatedDateTime, 113) AS [OPENED]

  , convert (VARCHAR,tsc.ChangeDate, 113) AS [CLOSED]

  , datediff (MINUTE,t.CreatedDateTime, tsc.ChangeDate) AS [SOLVING TIME IN MINUTES]

  , datediff (HOUR,t.CreatedDateTime, tsc.ChangeDate) AS [SOLVING TIME IN HOURS]

  , datediff (DAY,t.CreatedDateTime, tsc.ChangeDate) AS [SOLVING TIME IN DAYS]

  BTW... is there a way to convert the DATEDIFF so that it displays how long it took between the two dates in ONE column? (Like 1d 20h 34min.)

  Thanks in advance for the help...

  Regards

  Stef

  One option would be to create a scalar function. Scalar just means a function that returns one value for a set of parameters.

  For instance: dbo.GetLocalTime(@ticketDate, @timeZoneFrom, @timeZoneTo)

  So instead of:

  SELECT convert (VARCHAR,t.CreatedDateTime, 113)...

  You would have:

  SELECT convert(VARCHAR, dbo.GetLocalTime(t.CreatedDateTime, 'UTC', 'CET'), 113)...

  Fortunately, it looks like somebody has already written this function:

  http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/337b0ac4-2a35-4a42-ba08-6e84b18b38a0/

  Let's make it easier for those who may be interested:

  http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/337b0ac4-2a35-4a42-ba08-6e84b18b38a0/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 6, 2012 at 9:13 am

  #1534552

  There is a slight problem with that function if you look closely enough (besides the fact that it is a scalar function). There are three entries for CST. How do you know which one to use?