none
Change date format but keep as date data type

    问题

  • SQL 05

    Hi is it possible to convert a column issueDate to DD-MON-YY or DD-MON-YYYY but then change it to date data type?

    REPLACE(LEFT(CONVERT(VARCHAR(9), issueDate, 6),11),' ','-') AS [DD MON YY]

    This produces the format I want 02-Jun-12 but its varchar now, how can I change it back to date data type whilst keeping the format.

    I tired;

    CONVERT(DATETIME,REPLACE(LEFT(CONVERT(VARCHAR(9), issueDate, 6),11),' ','-')) AS [DD MON YY] but this gets

    2008-05-09 00:00:00.000

    Thanks


    • 已编辑 Cameronh 2012年7月6日 4:34
    2012年7月6日 4:33

答案

  • No.

    What you ask is like asking whether we can have a bald man, growing iut the hair, color it red, shave the hair off again so the man is bald again and keep the hair as red.

    Datetime data is stored as binary data. It doesn't have a format. The formatting is done but he client application. SQL Server just return binary data to the app, which in turn formats it to something which is readable to us human beings.


    Tibor Karaszi, SQL Server MVP | web | blog

    2012年7月6日 8:14

全部回复

  • Use

    REPLACE(LEFT(CONVERT(VARCHAR, issueDate, 106),11),' ','-') AS [DD-MON-YYYY]


    Many Thanks & Best Regards, Hua Min



    2012年7月6日 4:37
  • Why should you convert it back and forth.... Leave as it is and convert it to varchar only when required. Please let us know why you want to convert it to varchar and then to date again....

    Murali Krishnan

    2012年7月6日 4:39
  • Seems to work OK for me to convert it back right away

    select convert(datetime,REPLACE(LEFT(CONVERT(VARCHAR(9), CURRENT_TIMESTAMP, 6),11),' ','-')) AS [DD MON YY]
    Tested in SQL 2012 and SQL 2008 R2


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    2012年7月6日 4:40
  • Hi There

    Thanks for your posting. I think Mon type’s conversions, dependent on language setting

    SELECT convert(datetime, Getdate(), 106) -- dd mon yyyy

    So you have to set your Server control panel date like this if you would like to make it work otherwise you have to choose convert to varchar

    Many thanks

    Syed Qazafi Anjum

    2012年7月6日 4:56
  • With

    select convert(datetime,REPLACE(LEFT(CONVERT(VARCHAR(9), CURRENT_TIMESTAMP, 6),11),' ','-')) AS [DD MON YY]

    It returns 2008-05-09 00:00:00.000

    I need 09-MAY-08 as date data type, currently varchar

    I have this script in a SSIS package export to excel. But excel converts it's to string so the application doesn't like as its not a date data type.

    2012年7月6日 5:54
  • Can you try my way above?

    Many Thanks & Best Regards, Hua Min

    2012年7月6日 6:11
  • Hi Cameronh !

    Please try to understand MS SQL Server 2008 / 2005 stores Dates in ISO Data Format i.e; "YYYYMMDD". We have Date , DateTime Data Types in MS SQL Server to store date and time. Whatever format you supplies to Database it will eventually be stored in ISO Format as defined earlier.

    So do not try to CAST / CONVERT its format, just do this casting where you have to display this Date in the required format.

    Hope this clarifies your concept.

    Thanks, Hasham Niaz

    2012年7月6日 6:36
  • Why should you convert it back and forth.... Leave as it is and convert it to varchar only when required. Please let us know why you want to convert it to varchar and then to date again....

    Murali Krishnan


    I am happy to leave it as date data type as this is what I want. But I need it to display as 02-Jul-12.
    2012年7月6日 6:46
  • Hi Cameron.. I am not sure if there is a way to export the data to excel in the date format that you wish. Probably you can check it in Integration Services Thread. Why cant u store the data in Data format itself in excel rather than the varchar format...

    Murali Krishnan

    2012年7月6日 7:40
  • No.

    What you ask is like asking whether we can have a bald man, growing iut the hair, color it red, shave the hair off again so the man is bald again and keep the hair as red.

    Datetime data is stored as binary data. It doesn't have a format. The formatting is done but he client application. SQL Server just return binary data to the app, which in turn formats it to something which is readable to us human beings.


    Tibor Karaszi, SQL Server MVP | web | blog

    2012年7月6日 8:14