statement in t-sql

  • How the below can be written in SQL?

    CStr(Format([x],"dd-mmm-yyyy")) as y

    where x is a [smalldatetime] and y is [nvarchar](11)

  • Have you tried looking up CONVERT in BOL (Books Online)?

  • cast(cast('dd-mm-yyyy' as smalldatetime) as nvarchar(11))

  • gyessql (3/2/2009)


    cast(cast('dd-mm-yyyy' as smalldatetime) as nvarchar(11))

    Nope.

    convert(nvarchar(11), x, 106)

  • why can't this be used?

    cast(cast('dd-mmm-yyyy' as smalldatetime) as nvarchar(11))

    select cast(cast('21-jan-2009' as smalldatetime) as nvarchar(11)) use_cast,

    convert(nvarchar(11), '21-jan-2009', 106) use_convert

    which outputs:

    use_cast use_convert

    ----------- -----------

    Jan 21 200921-jan-2009

    If the output has to be in 'dd-mmm-yyyy' format, why convert and reconvert to the same?

  • Your CAST statement does not put the date into the format requested by the OP.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • gyessql (3/2/2009)


    why can't this be used?

    cast(cast('dd-mmm-yyyy' as smalldatetime) as nvarchar(11))

    select cast(cast('21-jan-2009' as smalldatetime) as nvarchar(11)) use_cast,

    convert(nvarchar(11), '21-jan-2009', 106) use_convert

    which outputs:

    use_cast use_convert

    ----------- -----------

    Jan 21 200921-jan-2009

    If the output has to be in 'dd-mmm-yyyy' format, why convert and reconvert to the same?

    It helps to read what was asked:

    How the below can be written in SQL?

    CStr(Format([x],"dd-mmm-yyyy")) as y

    where x is a [smalldatetime] and y is [nvarchar](11)

    In the above, x is a variable of the smalldatetime data type. The OP wants this converted to an nvarchar data type 11 characters long in the format of dd-mmm-yyyy. Now, the convert statement given does not have the '-' around the month, but that is easily fixed.

    The following code closely resembles what the OP is trying to do:

    declare @x as smalldatetime;

    set @x = '21-jan-2009';

    select @x, convert(nvarchar(11), @x, 106)

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

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