Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Datetime

Answered Datetime

  • Thursday, January 10, 2013 12:38 PM
     
     
    Sign In to Vote
    0

    I have a column in table A with datatype BIGINT which contains date in format  e.g. 20120101, 20121201 etc.

    In another table B I have column with datatype datetime which contains data in datetimeformat. I have converted that datetime to char using CONVERT as '2012-12-01' but how can I convrt his in form of 20121201.

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     

All Replies

  • Thursday, January 10, 2013 12:41 PM
    Answerer
     
     
    Sign In to Vote
    0
    DECLARE @dt INT=20120101

    SELECT CAST(CAST(@dt AS CHAR(8)) AS DATE)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Thursday, January 10, 2013 12:43 PM
     
     
    Sign In to Vote
    0

    Chk this

    DEclare @Date DateTime = '2012-12-01'
    Select convert(Varchar(10),@Date,112)

    Please have look on the comment


    • Edited by SanthoshH Thursday, January 10, 2013 12:43 PM
    •  
     
  • Thursday, January 10, 2013 12:46 PM
     
     
    Sign In to Vote
    0

    I have alreadt trie with this and it gives:

    select convert(varchar(11),'2013-01-10 17:22:21.297',112)

    Result: 2013-01-10 

    I want this in 2013010

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     
  • Thursday, January 10, 2013 12:47 PM
     
     
    Sign In to Vote
    0
    You send the opposite of it that I want :)

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     
  • Thursday, January 10, 2013 12:50 PM
     
     
    Sign In to Vote
    0

    Hey Kapil

    DEclare @Date DateTime = '2013-01-10 17:22:21.297'
    Select convert(Varchar(10),@Date,112)

    Result i am getting is 20130110

    Please have look on the comment

     
  • Thursday, January 10, 2013 12:51 PM
     
     Answered
    Sign In to Vote
    0

    I guess if u have a colum of varchar the query

    select convert(varchar(11),'2013-01-10 17:22:21.297',112) ll

    Result: 2013-01-10 

    So use this

    Select convert(Varchar(10),Convert(Datetime,@Date),112)

    Please have look on the comment

    • Marked As Answer by Kapil_KK Thursday, January 10, 2013 12:58 PM
    •  
     
  • Thursday, January 10, 2013 12:58 PM
     
     
    Sign In to Vote
    0

    oopes... 

    yes previously column was in datetime thn I convert it into varchar:

    select convert(varchar(11),'2013-01-10 17:22:21.297',112)

    i changed it into this:

    select convert(varchar(11),cast('2013-01-10 17:22:21.297' as DATE),112)

     it works..

    thanks

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh


    • Edited by Kapil_KK Thursday, January 10, 2013 12:58 PM
    •  
     
  • Thursday, January 10, 2013 1:01 PM
     
     
    Sign In to Vote
    0

    If you have a datetime  2016/07/25 and converting into varchar using formatter given below

    SELECT CONVERT(VARCHAR, GETDATE(), 112)   it give out put as 20160725

    You can also convert the varchar value 20160725 back to datetime

    Check the formatters here http://insqlserver.com/content/sql-datetime-formats

    Mark this post as answer if this resolves your issue.

    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin