none
date type conversion RRS feed

Answers

  • the above one giving me the error


    It could be that you have string values that cannot be converted to datetime or do not represent a datetime value correctly. Also, you need to cast the column to datetime before converting to string.

    select *
    from T
    where isdate(datrecv) = 0;

    select cast(convert(char(8), cast(datrecv as datetime), 112) as int) 
    form T;

    See the difference:

    SELECT
    	CONVERT(char(8), CAST('10/25/2010' AS datetime), 112) AS c1,
    	CONVERT(char(8), '10/25/2010', 112) AS c2
    GO

     


    AMB

     

    Some guidelines for posting questions...

    • Marked as answer by Ai-hua Qiu Wednesday, November 3, 2010 8:03 AM
    Monday, October 25, 2010 7:09 PM
    Moderator
  • Maybe the OP means the following:

    -- Convert date to integer format 
    DECLARE @v VARCHAR(20) 
    
    SET @v = '2010-10-10' 
    
    SELECT @v, 
      CONVERT(INT,REPLACE(@v,'-','')) 
      

    -- 2010-10-10 20101010



    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by Ai-hua Qiu Wednesday, November 3, 2010 8:03 AM
    Tuesday, November 2, 2010 8:35 PM
    Moderator

All replies

  • 2010-10-10 varchar datatype
    Monday, October 25, 2010 6:17 PM
  • What do you want as a result?

    declare @v varchar(20)
    set @v = '2010-10-10'
    select @v, CONVERT(datetime, @v, 120) as DateValue, CONVERT(int, convert(datetime, @v,120)) as IntValue
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, October 25, 2010 6:27 PM
    Moderator
  • select

     

    cast (convert(varchar(10),datrecv,112) as int)as ga

     

    from table name

     

    datrecv varchar datatype .

    Monday, October 25, 2010 6:44 PM
  • the above one giving me the error

    Monday, October 25, 2010 7:02 PM
  • the above one giving me the error


    It could be that you have string values that cannot be converted to datetime or do not represent a datetime value correctly. Also, you need to cast the column to datetime before converting to string.

    select *
    from T
    where isdate(datrecv) = 0;

    select cast(convert(char(8), cast(datrecv as datetime), 112) as int) 
    form T;

    See the difference:

    SELECT
    	CONVERT(char(8), CAST('10/25/2010' AS datetime), 112) AS c1,
    	CONVERT(char(8), '10/25/2010', 112) AS c2
    GO

     


    AMB

     

    Some guidelines for posting questions...

    • Marked as answer by Ai-hua Qiu Wednesday, November 3, 2010 8:03 AM
    Monday, October 25, 2010 7:09 PM
    Moderator
  • declare @var datetime
    set @var='2010-10-10'


    select convert(numeric(10,0),@var)
    select convert(int,@var)

    • Proposed as answer by Muhammad Abbas Tuesday, October 26, 2010 9:42 AM
    Tuesday, October 26, 2010 9:42 AM
  • How would you want that date represented as a number? The number of days since a certain date? The number of seconds since a certain date? Something else. If you can elaborate what this number would represent, then we can suggest something.
    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, October 26, 2010 10:59 AM
    Moderator
  • Any progress?
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, November 2, 2010 7:20 PM
    Moderator
  • What data type is it now? If it is a DATE, or a CHAR(n), your request makes no sense.  What is the integer value of Christmas? 
    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Tuesday, November 2, 2010 7:30 PM
  • Maybe the OP means the following:

    -- Convert date to integer format 
    DECLARE @v VARCHAR(20) 
    
    SET @v = '2010-10-10' 
    
    SELECT @v, 
      CONVERT(INT,REPLACE(@v,'-','')) 
      

    -- 2010-10-10 20101010



    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by Ai-hua Qiu Wednesday, November 3, 2010 8:03 AM
    Tuesday, November 2, 2010 8:35 PM
    Moderator