-1

I have a SQL Server table that has a datetime column. What I need to do with a SELECT statement is return (I think) a calculated column that takes the time from the datetime column and puts it together with the current date.

So in the table I may have...

[TargetPlot].[TargetDate]
2015-12-09 10:15:00
2015-12-09 10:30:00
2015-12-09 10:45:00
2015-12-09 11:00:00
2015-12-09 11:15:00

But what I need the SELECT statement to return is (assuming the current date is December 11th)...

[SomeCalculatedField]
2015-12-11 10:15:00
2015-12-11 10:30:00
2015-12-11 10:45:00
2015-12-11 11:00:00
2015-12-11 11:15:00

Thanks for any advice.

Andy

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andy
  • 383
  • 1
  • 6
  • 23

4 Answers4

1
Select CAST( CONVERT(VARCHAR(10), GETDATE(), 120) + ' ' +
          CONVERT(VARCHAR(8), TargetDate, 108) AS DATETIME) AS [SomeCalculatedField]
from TableName
M.Ali
  • 67,945
  • 13
  • 101
  • 127
0

You can do this by adding the dates, after suitable adjustment:

select cast(cast(getdate() as date) as datetime) + cast(targetdate as time)

SQL Server can add times to a datetime value.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I think you might want this

select (convert(varchar(10),getdate(),120))+' '+DATEADD(day, 0 - DATEDIFF(day, 0, column_name),Column_name) as SomeInput

from TableName

shaikh
  • 129
  • 1
  • 11
0

Not sure why you'd have to cast or convert anything.. you should just be able to add the difference in days from then to now.. 

DECLARE @Test TABLE (TargetDate DATETIME)
INSERT INTO @Test VALUES
('2015-12-09 10:15:00'),
('2015-12-09 10:30:00'),
('2015-12-09 10:45:00'),
('2015-12-09 11:00:00'),
('2015-12-09 11:15:00')


SELECT DATEADD(DAY, DATEDIFF(DAY, TargetDate, GETDATE()),TargetDate) FROM @Test
JamieD77
  • 13,796
  • 1
  • 17
  • 27