Post reply

Date formatting problem

  • February 9, 2006 at 12:39 pm

    #168230

    I have two columns in table1 defined as:

    col_mm_no smallint

    col_yr_no smallint

    The value of col_mm_no = 5

    The value of col_yr_no smallint = 2005

    I need to form a date out of these two columns using a default day of '01'.

    Here's what I have so far:

    select Convert(varchar(2),col_mm_no)+ '01' + Cast(col_yr_no as char(4)) as newdate

    from table1

    This is returning 5012005.

    What I want it to return is 05/01/2005 in a datetime format.

    How do I do this?

    thanks!

  • February 9, 2006 at 12:53 pm

    #619903

    Hello LS,

    Can you try this:

    select Convert(varchar(2),col_mm_no)+ '/' + '01' + '/' + Cast(col_yr_no as char(4)) as newdate from table1

    or

    select Convert(varchar(2),col_mm_no)+ '/01/' + Cast(col_yr_no as char(4)) as newdate from table1


    Lucky

  • February 9, 2006 at 1:11 pm

    #619911

    Lucky, thanks for the reply.  The problem is that I'm returning '5' when I really want '05'.

    thanks!

  • February 9, 2006 at 1:30 pm

    #619918

    print substring(convert(varchar(10),getdate(),2),4,2)+'/'+substring(convert(varchar(10),getdate(),2),7,2)+'/'+substring(convert(varchar(10),getdate(),2),1,2)

    Mathew J Kulangara
    sqladventures.blogspot.com

  • February 9, 2006 at 1:33 pm

    #619920

    DATEADD(mm, 5, DATEADD(YY, 2005-1900, 0))

    Than format this datetime value in a stile you want. See CAST in BOL.

    _____________
    Code for TallyGenerator

  • February 9, 2006 at 10:56 pm

    #620015

    should be DATEADD(month, 5 - 1, DATEADD(year, 2005-1900, 0))

    as DATEADD(year, 2005-1900, 0) will return 2005-01-01. Adding 5 months will make it 2005-06-01

  • February 9, 2006 at 11:12 pm

    #620018

     

    hi ls,

    you can use this,

    declare @mm_month int

    set @mm_month=5

    select len(@mm_month),str(@mm_month,2)

    select cast(replace(str(@mm_month,2),' ','0') as varchar(2)) + '/' + '01' + '/' + cast(year(getdate()) as varchar(4))

     

    you will get your answer!!!

     

     

  • February 10, 2006 at 6:56 am

    #620094

    Why not something as simple as:

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

    DECLARE @col_mm_no smallint,

      @col_yr_no smallint,

      @dtDate  datetime

    SET @col_mm_no = 5

    SET @col_yr_no = 2005

    SET @dtDate = CAST(@col_mm_no AS CHAR) + '/1/' + CAST(@col_yr_no AS CHAR)

    SELECT @dtDate

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

    RETURNS:

    2005-05-01 00:00:00.000

    Which is in datetime format.  Let the machine do the implied datatype conversion.


    maddog

  • February 10, 2006 at 7:10 am

    #620104

    If you want the result in varchar then

    CAST((@col_yr_no*10000)+(@col_mm_no*100)+1 as varchar)

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 10, 2006 at 10:32 am

    #620224

    This works if you need it as a character string

    declare @col_mm_no smallint

    declare @col_yr_no smallint

    select @col_mm_no = 5,

           @col_yr_no = 2005

    select right('0' + cast(@col_mm_no as varchar), 2) + '/01/'  + cast(@col_yr_no as varchar)

  • February 10, 2006 at 10:57 am

    #620232

    Here's three more variations:

    select

    convert(varchar, cast(cast(col_yr_no as varchar) + '-' + cast(col_mm_no as varchar) + '-01' as datetime), 101)

    select convert(varchar, cast(stuff(cast(col_yr_no * 100 + col_mm_no as varchar),5,0,'-') + '-01' as datetime), 101)

    select stuff(replace(str(10000 * col_mm_no + col_yr_no, 6), ' ', '0'), 3, 0, '/01/')

  • February 10, 2006 at 11:04 am

    #620235

    Somebody call PETA, this poor kitty's has been skinned plenty!   


    maddog

  • February 10, 2006 at 11:07 am

    #620236

    Thanks to all for the replies!

Viewing 13 posts - 1 through 12 (of 12 total)

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