none
How to cast string mm/yyyy to datetime?

    Question

  • Lets say I have a string "11/2012".  I want to convert it to a datetime in sql like 11/01/2012. How can I do that with CONVERT or CAST in TSQL? Thanks.
    Monday, February 11, 2013 5:11 PM

Answers

  • Regarding "it could be that ALL datetime fields are stored as mm/dd/yyyy HH:MM:SS.SSSS": Dates are stored as the number of days since Jan 1, 1900.

    For example SELECT CAST (0 AS DATETIME); returns the date 1900-01-01 00:00:00:00

    And SELECT CAST (4.5 AS DATETIME); returns the date 4 and 1/2 days later: 1900-01-05 12:00:00:00

    Todays date is SELECT GETDATE(), CAST (GETDATE() AS DECIMAL (12,6)); we are on day 41,314.

    The appearance of the date when you select it, depends on your default language settings, and you can format dates in many ways. See the Date and Time styles in the topic http://msdn.microsoft.com/en-us/library/ms187928.aspx

    This points out why you can't store mm/yyyy such as 11/2012. November 2012 isn't a date, it's a whole month. But November 1, 2012 is a date. Now you have a problem. Do you want to store November 2012 as November 1, 2012 or use some other system to actually store November 2012. It depends on your application.

    CONS: If you use a DATETIME data type, you risk the possibility of somebody entering November 20, 2012 and messing up your data.

    PROS: On the other hand, you can use date functions such as DATE ADD to do math with dates. For example; how many days between June 1 and November 1 is an easy question when stored as a DATETIME, but can get ugly if you don't use DATETIME and have to figure out how many days in each month (including leap year). Date and time functions are listed here http://msdn.microsoft.com/en-us/library/ms186724.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, February 11, 2013 10:23 PM
    Answerer

All replies

  • In the format that you have it will not work.  You have to have at the least a month, day, and year:

    SELECT CONVERT(DATETIME, '11/01/2012') AS dt
    SELECT CAST('11/01/2012' AS DATETIME) AS dt


    David Dye My Blog

    Monday, February 11, 2013 5:16 PM
    Moderator
  • Thanks.

    This will work for me and convert mm/yyyy to mm/dd/yyyy HH:MM:SS.SSSS (or null when the original field is empty)

    (case ManufactureDate 
    when '' then NULL 
    else CAST((SUBSTRING(ManufactureDate,1,2) + '/01' + SUBSTRING(ManufactureDate,3,5)) as datetime) 
    end) as ManufactureDate

    However, what i I want the data to be stored just as mm/dd/yyyy? I am new to sql so it could be that ALL datatime fields are stored as mm/dd/yyyy HH:MM:SS.SSSS and just retrieved differently? 


    • Edited by BBauer42 Monday, February 11, 2013 6:31 PM
    Monday, February 11, 2013 6:19 PM
  • Thanks.

    This will work for me and convert mm/yyyy to mm/dd/yyyy HH:MM:SS.SSSS (or null when the original field is empty)

    (case ManufactureDate 
    when '' then NULL 
    else CAST((SUBSTRING(ManufactureDate,1,2) + '/01' + SUBSTRING(ManufactureDate,3,5)) as datetime) 
    end) as ManufactureDate

    However, what i I want the data to be stored just as mm/dd/yyyy? I am new to sql so it could be that ALL datatime fields are stored as mm/dd/yyyy HH:MM:SS.SSSS and just retrieved differently? 


    Did  you say you want only mm/dd/yyyy instead of mm/dd/yyyy HH:MM:SS.SSSS?? Try changing the data type to DATE instead of datetime. What version of sql are using? I think Date datatype is available in sql 2008 and later.

    Try this : if you are using sql 2005

    (case ManufactureDate
     when '' then NULL
     else convert(varchar(10),convert(datetime,'01/'+ManufactureDate,103),101)
     end) as ManufactureDate


    Hope it Helps!!



    • Edited by Stan210Editor Monday, February 11, 2013 7:45 PM
    • Proposed as answer by jhoop2002 Wednesday, November 13, 2013 7:35 PM
    Monday, February 11, 2013 7:22 PM
    Answerer
  • Regarding "it could be that ALL datetime fields are stored as mm/dd/yyyy HH:MM:SS.SSSS": Dates are stored as the number of days since Jan 1, 1900.

    For example SELECT CAST (0 AS DATETIME); returns the date 1900-01-01 00:00:00:00

    And SELECT CAST (4.5 AS DATETIME); returns the date 4 and 1/2 days later: 1900-01-05 12:00:00:00

    Todays date is SELECT GETDATE(), CAST (GETDATE() AS DECIMAL (12,6)); we are on day 41,314.

    The appearance of the date when you select it, depends on your default language settings, and you can format dates in many ways. See the Date and Time styles in the topic http://msdn.microsoft.com/en-us/library/ms187928.aspx

    This points out why you can't store mm/yyyy such as 11/2012. November 2012 isn't a date, it's a whole month. But November 1, 2012 is a date. Now you have a problem. Do you want to store November 2012 as November 1, 2012 or use some other system to actually store November 2012. It depends on your application.

    CONS: If you use a DATETIME data type, you risk the possibility of somebody entering November 20, 2012 and messing up your data.

    PROS: On the other hand, you can use date functions such as DATE ADD to do math with dates. For example; how many days between June 1 and November 1 is an easy question when stored as a DATETIME, but can get ugly if you don't use DATETIME and have to figure out how many days in each month (including leap year). Date and time functions are listed here http://msdn.microsoft.com/en-us/library/ms186724.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, February 11, 2013 10:23 PM
    Answerer
  • Thank you!!!


    jhoop

    Wednesday, November 13, 2013 7:37 PM