How to Merge dd,mm,yyyy hh,mm,ss(URGENT !!!)

• SPtrt

  SSChasing Mays

  Points: 603

  More actions

  January 31, 2011 at 7:48 am

  #230926

  Hi

  Hi i have seperated dd,mm,yyy and hh,mm, ss.

  My Reqiuremet;

  I have data which is storing the "VALUE COUNT" every 30 min with "ID". so i have to merge that 30min to hourly storage to reduce memory consumption. So i seperated the date and time in hour. Now i need to merge date and hour along with minute as ':00'. I'm struggling a lot. plse any help......

  my query

  Select ID,CONVERT(varchar(8), date, 112) as DATE, convert(varchar(2), Datepart(hh, Date)) as hr, sum(Value) as Value from dbo.Traffics_Testing group by CONVERT(varchar(8), date, 112), Datepart(hh, Date) , Id order by CONVERT(varchar(8), date, 112), Datepart(hh, Date), Id

  Result

  ID DATE HR VALUE

  1 201001011 0 23

  2 20101011 2 70

  2 20101218 12 25

  3 20101218 13 50

  REQUIRED OUTPUT:

  ================

  I need to merge date and hour:mm

  eg)

  1)20100101 00:00:00

  2)20100101 02:00:00

  ANy help. plse its urgent. Thanks For your kind help.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 31, 2011 at 8:39 am

  #1280457

  what you want to do is to use the DATEADD function, which allows you to add to an existing datetime value.

  in the below example, i'm adding hours (hh); note i took the time to convert YOUR data into a format that anyone reading the psot can use by copying and pasting directly into SSMS; if you can do the smae in the future, you'll get better answers, since there would be no abiguity about the real column names, data types, etc, and everyone ahs the basics to start testing a solution that might help.

  /*

  12010-01-11 00:00:00.00023

  22010-10-11 02:00:00.00070

  22010-12-18 12:00:00.00025

  32010-12-18 13:00:00.00050

  */

  With myCTE AS (

  SELECT '1' AS ID,'20100111' AS DATE,0 AS HR,'23' AS VALUE UNION ALL

  SELECT '2','20101011',2,'70' UNION ALL

  SELECT '2','20101218',12,'25' UNION ALL

  SELECT '3','20101218',13,'50' )

  SELECT

  ID,

  DATEADD(hh,HR,[Date]) As TheDate,

  Value

  From myCTE

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 31, 2011 at 9:30 am

  #1280484

  You can just add them together. Example:

  select

  a.[Date]+a.[Time] as DatePlusTime,

  a.*

  from

  (

  select -- Test Data

  [Date] = convert(datetime,'2010-09-29 11:53:00') ,

  [Time] = convert(datetime,'01:00:00')

  ) a

  Results:

  DatePlusTime Date Time

  ----------------------- ----------------------- -----------------------

  2010-09-29 12:53:00.000 2010-09-29 11:53:00.000 1900-01-01 01:00:00.000