none
Equal from Datetime and Dateadd(hour,12,getdate())

    Question

  • Hi, here is my problem, when im trying to compare  Datetime and Dateadd(hour,12,getdate()) i have this error :

     

    Conversion failed when converting date and/or time from character string.

     

    here is the code:

    select CONVERT(datetime,convert(nvarchar(20),convert(date,getdate())) + ' ' + CONVERT(nvarchar(20), [time]),120) from dbo.Alarms 
    where CONVERT(datetime,convert(nvarchar(20),convert(date,getdate())) + ' ' + CONVERT(nvarchar(20), [time])) < DATEADD(HOUR,12,GETDATE())
    
    

    Please help me, I need to solve this problem faster.

     

    Thanks, 

     

    Nichovski Bogdan

    Thursday, February 24, 2011 1:59 PM

Answers

  • hi,

    your CONVERT()s are incorrect. btw, what type is [time]?

    E.g.

    SELECT  GETDATE() AS Plain ,
            CAST(CAST(GETDATE() AS DATE) AS DATETIME) +
                 CAST(CAST(GETDATE() AS TIME) AS DATETIME) AS Casted ;

    In your case in the field list and where condition:

      CAST(CAST(GETDATE() AS DATE) AS DATETIME) +
           CAST([Time] AS DATETIME)

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, February 24, 2011 2:09 PM

All replies

  • hi,

    your CONVERT()s are incorrect. btw, what type is [time]?

    E.g.

    SELECT  GETDATE() AS Plain ,
            CAST(CAST(GETDATE() AS DATE) AS DATETIME) +
                 CAST(CAST(GETDATE() AS TIME) AS DATETIME) AS Casted ;

    In your case in the field list and where condition:

      CAST(CAST(GETDATE() AS DATE) AS DATETIME) +
           CAST([Time] AS DATETIME)

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, February 24, 2011 2:09 PM
  • hi Nichovski,

    Please help me, I need to solve this problem faster.

    hmm, :) faster then we can give you tips, hints and clues?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, February 24, 2011 2:14 PM
  • Thanks Stefan, finally i solved my problem, Now i will test sometime and later i will write again. Thanks again
    Thursday, February 24, 2011 2:21 PM