locked
convert datetime to varchar and again to datetime RRS feed

  • Question

  • Hi all,

    I have a datetime column with value 2010-06-23 03:30:00.000

    I need to convert this to varchar.after converting to varchar then again i need to convert the output to datetime2(3).

    How can i do this.

    thanks in advance!!

    • Moved by Tom Phillips Monday, October 25, 2010 6:45 PM TSQL Question (From:SQL Server Database Engine)
    Monday, October 25, 2010 6:10 PM

Answers

  • Take a look at this topic in BOL: CAST & CONVERT. Make sure to use the correct code for both side conversions.

    Take a look at this sample:

    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
    • Proposed as answer by Muhammad Abbas Tuesday, October 26, 2010 7:29 AM
    • Marked as answer by Ai-hua Qiu Wednesday, November 3, 2010 8:02 AM
    Monday, October 25, 2010 6:30 PM

All replies

  • Take a look at this topic in BOL: CAST & CONVERT. Make sure to use the correct code for both side conversions.

    Take a look at this sample:

    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
    • Proposed as answer by Muhammad Abbas Tuesday, October 26, 2010 7:29 AM
    • Marked as answer by Ai-hua Qiu Wednesday, November 3, 2010 8:02 AM
    Monday, October 25, 2010 6:30 PM
  • Hi Leo,

    Why do you need to use varchar? Why you cannot simply convert it to datetime2?

     

    declare
    	@D datetime = '2010-06-23 03:30:00.000'
    	
    select @D, CONVERT(datetime2(3),@D)
    


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, October 25, 2010 6:44 PM
  • Am getting data from 2005 server's msdb.dbo.sysjobactivity and trying to insert those data into 2008 server's staging table stgsysjobactivity which has datatype datetime2(3).Its throwing error :error convertting dbtype_dbtimestamp to datetime2.

    thats why am trying to convert datetime into string and then to datetime and then insert into 2008 server's staging table.

    Monday, October 25, 2010 10:51 PM