locked
mm/dd/yy to mm/dd/yyyy RRS feed

  • Question

  • hi guys,

    Help me.I have a situation to convert  mm/dd/yy to mm/dd/yyyy when somebody enters date.

    Thank you!

    Monday, October 7, 2013 11:24 PM

Answers

  • The best place is to do at your presentation layer and its is easy there. However, just giving you the syntax for the same in SQL Server as below.

    Try the below:

    Select Convert(Varchar(20),CAST('10/08/13' as DATE),101)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, October 7, 2013 11:50 PM

All replies

  • The best place is to do at your presentation layer and its is easy there. However, just giving you the syntax for the same in SQL Server as below.

    Try the below:

    Select Convert(Varchar(20),CAST('10/08/13' as DATE),101)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, October 7, 2013 11:50 PM
  • perfect.

    Thanks you!

    Monday, October 7, 2013 11:58 PM
  • Use international ANSI date string literal: YYYY-MM-DD or YYYYMMDD.

    Example: DECLARE @Date date = '2013-09-07'

    In tables, use DATE or DATETIME.

    Datetime conversion: http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, October 8, 2013 12:06 AM
  • Try

    select convert(varchar,CONVERT(datetime,'10/13/13',1),101)
    go
    


    Many Thanks & Best Regards, Hua Min


    Tuesday, October 8, 2013 1:37 AM
  • Select Convert(Varchar(20),CAST('10/08/13' as DATE),101)


    Latheesh, may better with format option 1 instead of 101; see CAST and CONVERT (Transact-SQL) => Remarks => Without century (yy) / With century (yyyy)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Harsh Kumar Tuesday, October 8, 2013 6:55 AM
    Tuesday, October 8, 2013 6:42 AM
  • I am surprised you guys giving advice to convert from one disaster date string literal format to another disaster format without mentioning that mm/dd/yy (UK: dd/mm/yy) should be avoided.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, October 8, 2013 6:58 AM
  • I am surprised you guys giving advice to convert from one disaster date string


    The OP get's what the OP asks for; but I don't want to forget to mention how SQL Server handles 2 digit years: Configure the two digit year cutoff Server Configuration Option

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 8, 2013 7:21 AM
  • Select Convert(Varchar(20),CAST('10/08/13' as DATE),101)


    Latheesh, may better with format option 1 instead of 101; see CAST and CONVERT (Transact-SQL) => Remarks => Without century (yy) / With century (yyyy)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    But OP is expecting mm/dd/yyyy format. 1 will give you mm/dd/yy. Am I wrong, please correct me.

    Select Convert(Varchar(20),CAST('10/08/13' as DATE),101) --10/08/2013 (Expected one)
    
    Select Convert(Varchar(20),CAST('10/08/13' as DATE),1)   --10/08/13 ( Not expected)
    May be, I assume its not 1913....:) Thats why I asked OP to do this formatting at Presentation layer as we are not sure of the format of the date.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by SQLZealots Tuesday, October 8, 2013 7:27 AM
    Tuesday, October 8, 2013 7:24 AM
  • I am surprised you guys giving advice to convert from one disaster date string


    The OP get's what the OP asks for; but I don't want to forget to mention how SQL Server handles 2 digit years: Configure the two digit year cutoff Server Configuration Option

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Good info!!! +1

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, October 8, 2013 7:29 AM
  • Latheesh, sorry, for the string format of the result you are right. Somehow I missed that you used CAST to convert string to date; here you may better use CONVERT with format option 1 like

    Select Convert(Varchar(20)
                  ,Convert(DATE, '10/08/13', 1)
                  ,101)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 8, 2013 7:48 AM
  • >Help me.I have a situation to convert  mm/dd/yy to mm/dd/yyyy when somebody enters date.

    Why do you want to convert it from bad string format to another bad string format?

    How about using DATE or DATETIME?


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, October 8, 2013 8:32 AM