none
How to add date and time into datetime in sql 2012

    Question

  • This used to work in sql 2008 but in sql 2012 it's giving an error. How can I add a date and time into a datetime data type?

    Thank you

    SELECT CAST(GETDATE() AS DATE) + CAST(GETDATE() AS TIME)
    Msg 8117, Level 16, State 1, Line 1
    Operand data type date is invalid for add operator.




    Gokhan Varol

    Thursday, March 08, 2012 9:25 PM

Answers

  • select cast(cast(getdate()astime)asdatetime)+cast(cast(getdate()asdate)asdatetime),getdate()

    Wednesday, August 01, 2012 8:30 PM
  • DECLARE	@Now DATETIME = GETDATE(),
    	@DatePart DATE = GETDATE(),
    	@TimePart TIME(3) = GETDATE();
    
    -- SwePeso
    SELECT	@Now AS [Now],
    	@DatePart AS [DatePart],
    	@TimePart AS TimePart,
    	DATEADD(DAY, DATEDIFF(DAY, @TimePart, @DatePart), CAST(@TimePart AS DATETIME)) AS Result


    N 56°04'39.26"
    E 12°55'05.63"

    Tuesday, August 07, 2012 8:29 PM

All replies

  • That statement does not work for me in 2008 R2.  Try casting the first portion to datetime:

    select cast(cast(getdate() as date) as datetime) + cast(getdate() as time)
    Does that work?

    website: jontav.com | Blog

    • Proposed as answer by Naomi NModerator Thursday, March 08, 2012 10:11 PM
    • Unproposed as answer by Gokhan Varol Thursday, March 08, 2012 10:12 PM
    Thursday, March 08, 2012 9:42 PM
  • SELECT CAST(GETDATE() AS DATE) + CAST(GETDATE() AS TIME)
    Msg 8117, Level 16, State 1, Line 1
    Operand data type date is invalid for add operator.

    Does exactly the same in Server 2008 R2: Throwign an error, that you can add to Date type.

    Edit:
    This also throws the same error:
    DECLARE @Date Datetime = CAST(GETDATE() AS DATE) + CAST(GETDATE() AS TIME);
    
    SELECT @Date;


    Thursday, March 08, 2012 9:45 PM
  • I build a valid date time string and then casted that into datetime, I don't like it at all though, I wish there is a more elegant way doing this.

    SELECT  run_date,
            run_time,
    		CAST(CAST(run_date AS VARCHAR) + ' ' + STUFF(STUFF(RIGHT('0' + CAST([run_time] AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
    FROM    [msdb].[dbo].[sysjobhistory]
    


    Gokhan Varol

    Thursday, March 08, 2012 9:49 PM
  • this code used to work in sql 2008, it broke in sql 2012 and I am trying to rewrite it

    SELECT  TOP 10 
    		CAST(CAST(jh.[run_date] AS VARCHAR) AS DATE) + CAST(CAST(STUFF(STUFF(RIGHT('0' + CAST(jh.[run_time] AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS TIME) AS DATETIME),
            @@version
    FROM    msdb.[dbo].[sysjobhistory] jh
    I rewrote it as below (I don't like it though what do you think)
    SELECT  run_date,
            run_time,
    		CAST(CAST(run_date AS VARCHAR) + ' ' + STUFF(STUFF(RIGHT('0' + CAST([run_time] AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
    FROM    [msdb].[dbo].[sysjobhistory]


    Gokhan Varol

    Thursday, March 08, 2012 10:06 PM
  • That is very odd. The rules for implicit conversions for Sever 2012 say, that time and date will be implicitly casted to datetime.

    Thursday, March 08, 2012 10:06 PM
  • select cast(cast(getdate()astime)asdatetime)+cast(cast(getdate()asdate)asdatetime),getdate()

    Wednesday, August 01, 2012 8:30 PM
  • I fixed the previous solution. It works.

    select cast(cast(getdate()as time)as datetime)+cast(cast(getdate()as date)as datetime),getdate()
    --2012-08-07 14:49:45.590	2012-08-07 14:49:45.590
    


    Kalman Toth SQL SERVER 2012 & BI TRAINING

    Tuesday, August 07, 2012 6:51 PM
    Moderator
  • See my article here

    http://www.sqltopia.com/?page_id=35


    N 56°04'39.26"
    E 12°55'05.63"

    Tuesday, August 07, 2012 8:24 PM
  • DECLARE	@Now DATETIME = GETDATE(),
    	@DatePart DATE = GETDATE(),
    	@TimePart TIME(3) = GETDATE();
    
    -- SwePeso
    SELECT	@Now AS [Now],
    	@DatePart AS [DatePart],
    	@TimePart AS TimePart,
    	DATEADD(DAY, DATEDIFF(DAY, @TimePart, @DatePart), CAST(@TimePart AS DATETIME)) AS Result


    N 56°04'39.26"
    E 12°55'05.63"

    Tuesday, August 07, 2012 8:29 PM
  • This works,

    SELECT cast(getdate() as datetime)+cast('23:59:59' as datetime)


    • Edited by Bradley_S Friday, July 18, 2014 10:32 PM Autoformating removed spaces
    Friday, July 18, 2014 10:32 PM
  • select cast(cast(getdate()astime)asdatetime)+cast(cast(getdate()asdate)asdatetime),getdate()


    More complex than needed but it works also.
    Friday, July 18, 2014 10:34 PM