Sql datatype conversion

Question

we need to change one column i.e., Start_Date of datatype datetime to bigint. For existing datas we have records like '2010-01-01 00:00:00' which should be converted into bigint and should return value like '20100101000000'. I tried with convert and cast fn but i am not getting the desire output. Can anyone help me out on this.

Answer 1

First convert datetime to varchar.

Then do a replace for '-' with empty char ''

Answer 2

Hope This will help

    SELECT  DATENAME(year, Datetime_Colum)+''+
            SUBSTRING( CAST(CONVERT(DATE,Datetime_Colum,101)AS VARCHAR(8)),6,2)+''+
            DATENAME(DAY, Datetime_Colum)+''+
            DATENAME(hour, Datetime_Colum)+''+
            DATENAME(minute, Datetime_Colum)+''+
            DATENAME(second, Datetime_Colum)
    FROM    [dbo].[Table]

--//===================================================================

Function:

CREATE FUNCTION udf_Convert_Datetime_To_INT 
(
    @DATETIME DATETIME
)

RETURNS BIGINT
AS
BEGIN

DECLARE @Result BIGINT

SELECT @Result =   CAST(( DATENAME(YEAR,@DATETIME) +''+
                    SUBSTRING( CAST(CONVERT(DATE,GETDATE(),101)AS VARCHAR(8)),6,2)+''+
                    DATENAME(DAY,@DATETIME)+''+
                    DATENAME(hour, @DATETIME)+''+
                    DATENAME(minute, @DATETIME)+''+
                    DATENAME(second, @DATETIME)) AS BIGINT)
RETURN @Result

END
GO

PRINT DBO.udf_Convert_Datetime_To_INT (GETDATE())

Result : 20110511152843

Answer 3

update Table_1 set xnew=REPLACE(REPLACE(REPLACE (CONVERT(VARCHAR,x,20) , '-' , '' ),':',''),' ','')

add new field xnew varchar(50) , then you can convert it into bigint.. x is your datetime field..