Format a timestamp to a proper string loses the second added

  • I have the following dynamic sql that adds a second to a timestamp and then adds quotes with the quotename function:

    'INSERT INTO ' + N'' + QUOTENAME(TABLE_SCHEMA) +

    N'.' + QUOTENAME(TABLE_NAME) +

    N' SELECT T.*,' + QUOTENAME(@STAMP,'''') ........................

    @stamp is defined as:

    CONVERT(VARCHAR(23),DATEADD(SS,@global_time,CAST(Floor(CAST(Getdate() AS FLOAT)) AS DATETIME)),120)

    The returned value is:

    'Sep 16 2008 12:00AM'

    instead of:

    '2008-09-16 00:00:01'

    So ,I basically lose the second again.

    Any thoughts?

  • If you print out the dynamic SQL before you exec it, what do you see?

    I tested a section of your code out

    DECLARE @Stamp varchar(25), @global_time int

    SET @global_time = 25

    set @stamp = CONVERT(VARCHAR(23),DATEADD(SS,@global_time,CAST(Floor(CAST(Getdate() AS FLOAT)) AS DATETIME)),120)

    print N' SELECT T.*,' + QUOTENAME(@STAMP,'''')

    and it seems to return fine

    SELECT T.*,'2008-09-16 00:00:25'

    Where are you seeing the 'Sep 16 2008 12:00AM' value? When selecting from the table later?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • No,

    When I take the entire SQL string and run it, then the result is:

    INSERT INTO [dbo].[H_DATUM_KEY] SELECT T.*,'Sep 16 2008 12:00AM' FROM [dbo].[V_H_I_DATUM_KEY] T

    When I take your example the result is:

    SELECT T.*,'2008-09-16 00:00:25'

    which is what I want.

    I am baffled..

  • Sorry,

    Must have been the lack of coffee.

    I had @stamp defined as TIMESTAMP, if I define it as VARCHAR(23) then all is as should be..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply