Get rid of duplicated timestamp

• daniel.sousa

  SSC Enthusiast

  Points: 153

  More actions

  January 24, 2017 at 9:43 am

  #334458

  Hello, 

  I wan't to create a timestamp with a "Begin" to an "End" date.
  I created a Table called 'numbers' in MSSQL 2008 with the numbers from 0 to 23 (code bellow):

  CREATE TABLE [numbers]
([num] int);

  INSERT INTO numbers
([num])

  VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
(21),(22),(23);

  After creating that I wrote the following code to an SQL Query:

  SELECT "num",

  dateadd(hour, (num), DATEDIFF(d, 0, GETDATE())) AS interval_start,
dateadd(hour, (num + 1), DATEDIFF(d, 0, GETDATE())) AS interval_end

  FROM numbers
GO

  The result that I'm getting is the following:

  

  As You can see, my timestamp is writing the same hour intervals tree times! 

  Is there anything I can implement in my code so I can fix this?

  Thank You very much.
  Daniel

• Thom A

  SSC Guru

  Points: 99143

  More actions

  January 24, 2017 at 9:49 am

  #1924664

  Looking at your SQL I wouldn't have expected it to give me 3 duplicates, and it didn't when i tried.

  This sounds like you've possibly triplicated your data in your numbers table. If you do a simple SELECT * FROM [numbers]; how many rows do you get?

  I'd suggest dropping and recreating your table will fix this.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 24, 2017 at 9:51 am

  #1924666

  Daniel

  My guess is that you ran your INSERT statement three times, and the triplication originates in your numbers table.  I ran the code you posted and I only got one row for each interval.

  John