using parameter and not using parameter , the result is different

  • 892717952

    Mr or Mrs. 500

    Points: 515

    declare @YYMMDD DATETIME

    set @YYMMDD='2020-09-23 00:00:00.000'

    select CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112) , CONVERT(VARCHAR(6), @YYMMDD, 112)

    same value, when we use parameter to pass the value, and not using parameter, the result is not same, what is the reason ? thanks!

     

    datetime convert

     

     

     

     

  • anthony.green

    SSC Guru

    Points: 112519

    Because your using string literals to represent a datetime format.

    CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112)

    The above is taking a string and converting it into a 6 character string.

    Datetimes are represented as an integer value positive or minus the default date representing how much time has elapsed since the default, not strings.

    If you convert the string to DATETIME, then to VARCHAR(6) you see you get the same as using the parameter

    declare @YYMMDD DATETIME

    set @YYMMDD='2020-09-23 00:00:00.000'

    select
    CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112) ,
    CONVERT(VARCHAR(6), CONVERT(DATETIME,'2020-09-23 00:00:00.000'), 112) ,
    CONVERT(VARCHAR(6), @YYMMDD, 112)

     

  • 892717952

    Mr or Mrs. 500

    Points: 515

    anthony.green wrote:

    Because your using string literals to represent a datetime format.

    CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112)

    The above is taking a string and converting it into a 6 character string.

    Datetimes are represented as an integer value positive or minus the default date representing how much time has elapsed since the default, not strings.

    If you convert the string to DATETIME, then to VARCHAR(6) you see you get the same as using the parameter

    declare @YYMMDD DATETIME

    set @YYMMDD='2020-09-23 00:00:00.000'

    select
    CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112) ,
    CONVERT(VARCHAR(6), CONVERT(DATETIME,'2020-09-23 00:00:00.000'), 112) ,
    CONVERT(VARCHAR(6), @YYMMDD, 112)

     

    noted , thanks you for your great help!

  • frederico_fonseca

    SSChampion

    Points: 14782

    and 112 is wrong format for that date representation.

    112 = YYYYMMDD

    121 = YYYY-MM-DD HH:MI:SS.mmm

     

  • o.schoen

    SSC Rookie

    Points: 39

    And maybe for the task on hand the code 12 would be correct for ISO "yymmdd" format:

    DECLARE @YYMMDD DATETIME
    SET @YYMMDD='2020-09-23 00:00:00.000' /* converts literal string to DATETIME */
    SELECT CONVERT(VARCHAR(6), @YYMMDD, 12) /* converts DATETIME back to VARCHAR */

    This results in the '200923' output as probably intended.

  • 892717952

    Mr or Mrs. 500

    Points: 515

    thank you for your kind help!

Viewing 6 posts - 1 through 6 (of 6 total)

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