Post reply

How to convert string to "data type" ?

  • May 10, 2017 at 4:06 pm

    #1942104

    You should look into using sql_variant data type, it could help you do this.  You'd have to use the desired data type, or cast to an explicit type, when loading the data, after that the column itself would "remember" what data type it was.  For example, see below.

    This is not to say that this design is good or not good, just that there could be a technical approach that makes it easier to do.


    USE tempdb;
    SET NOCOUNT ON;
    GO
    CREATE TABLE dbo.test1 (
      col1 sql_variant NOT NULL,
      col2 sql_variant NULL
      )
    GO
    INSERT INTO test1
    SELECT 1, GETDATE()
    INSERT INTO test1
    SELECT 'abc', NEWID()
    GO
    SELECT *, SQL_VARIANT_PROPERTY(col1, 'BASETYPE') AS col1_data_type, SQL_VARIANT_PROPERTY(col2, 'BASETYPE') AS col2_data_type FROM test1
    GO
    DROP TABLE dbo.test1
    SET NOCOUNT OFF;

    Edit: Added columns to show the basetype in the output query.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing post 16 (of 15 total)

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