Results 1 to 7 of 7

Thread: Convert String

  1. #1
    Join Date
    Oct 2009
    Posts
    93

    Unanswered: Convert String

    Have the following data in a table that i must convert to smalldatetime

    201001
    201002
    201003

    201001 >>> Must be converted to 01/01/2010
    201002 >>> Must be converted to 01/02/2010
    201003 >>> Must be converted to 01/02/2010

    I could perhaps do this in Excel before import but is there any way i can contruct this conversion in a View

    Any help appreciated

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    SELECT CONVERT(SMALLDATETIME, '201001' + '01', 112)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2009
    Posts
    93
    Thanks Wim,

    But how do i convert several all at the same time ? Just continue query like below ?

    SELECT CONVERT(SMALLDATETIME, '201001' + '01', 112),CONVERT(SMALLDATETIME, '201002' + '02', 112)

    Also if i have the following for 2011
    201101 >>>> 01/01/2011 -- CONVERT(SMALLDATETIME, '201101' + '01', xxx?)

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by sullyman
    ...
    But how do i convert several all at the same time ?
    ....
    I don't know how to answer that question. In the first post on this thread you wrote
    Quote Originally Posted by sullyman
    Have the following data in a table that i must convert to smalldatetime
    So I guess those "several" are already in that table, and you can access them "all at the same time" as you can do with any column in any table:
    Code:
    DROP TABLE #DaTable
    CREATE TABLE #DaTable(
    	AString	CHAR(6)	NOT NULL,
    	ASmallDatetime	SmallDateTime
    )
    
    INSERT INTO #DaTable(AString)
    VALUES 
    ('201001'),
    ('201002'),
    ('201003'),
    ('201101')
    
    SELECT AString, ASmallDatetime, CONVERT(SMALLDATETIME, AString + '01', 112)
    from #DaTable
    
    UPDATE #DaTable
    SET ASmallDatetime = CONVERT(SMALLDATETIME, AString + '01', 112)
    
    SELECT * from #DaTable
    If you run the above SQL code, you will see that '201101' is not a special case.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Oct 2009
    Posts
    93
    Hi Wim,

    Thanks. I cannot get this to work in a View.

    It gives the error 'Conversion failed when converting character string to smalldatetime data type'

    field i am trying to convert is varchar(8)
    phdate is smalldatetime field

    SELECT CC, CONVERT(SMALLDATETIME, 'Period' + '01', 112) AS phdate
    FROM dbo.D_Table


    any idea

  6. #6
    Join Date
    Oct 2009
    Posts
    93
    Thanks Wim,

    I ran it as a query and got it to work. I can now use the data for my view

    USE DB
    GO
    SELECT Period, PHdate, CONVERT(SMALLDATETIME, Period + '01', 112)
    from D_Table
    update D_Table
    SET PHdate = CONVERT(SMALLDATETIME, Period + '01', 112)
    Go

  7. #7
    Join Date
    Sep 2011
    Posts
    75

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •