none
Converting varchar to DateTime

    Question

  •  

    Hi,

     

    I wanted to convert the varchar to date time and here is what i am doing

     

    DECLARE @dt VARCHAR(20)

    SET @dt = '20070111'  -- YYYYMMDD format

    select CONVERT(datetime, @dt, 120)

     

    This works perfectly fine and the result would be-  2007-01-11 00:00:00.000

     

    But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing

    "Conversion failed when converting datetime from character string."

     

    Can any one please let me know how do we achieve this?

     

     

    ~Mohan

    Monday, September 17, 2007 9:37 PM

Answers

  •  

    YYYYMMDDHHMM is not recognized as a valid datetime string. For example, YYYMMDD HH:MM works.

    Monday, September 17, 2007 10:05 PM

All replies

  •  

    YYYYMMDDHHMM is not recognized as a valid datetime string. For example, YYYMMDD HH:MM works.

    Monday, September 17, 2007 10:05 PM
  •  

    This is the Convert sintax:

    CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

     

    The first parameter data_type is the required convertion type, of course including the length if required. The second parameter is the expression to convert, and to endding the last parameter is used to define the style in that you are passing the "expression" parameter.

     

    In your code, the style parameter says 120 that corresponds to a ODBC Canonical format in this format: yyyy-mm-dd hh:miTongue Tieds.

     

    Then, ¿Does because a string with the format yyyymmdd can be converted to string?:

    The YYYYMMDD is widely recognized ODBC String Format that can be used to convert a string to a SQL Server DateTime format. When you use this format, the convert function ignores the last parameter, because it's a standard format. Instead, YYYMMDDHHMM is not a SQL Server recognized format, by this you can not use this.

     

    I recommend to you, to pass strings in the yyyy-mm-dd hh:miTongue Tieds format to be recognized by the CONVERT or CAST functions in SQL server.

    Monday, September 17, 2007 10:27 PM