none
Converting from dd.mm.yyyy to YYYY-MM-DD

    Question

  • Hi,

     

    I have a requirement as follows:

    in my DB,  i have a column in which i loaded the date data as dd.mm.yyyy. the column data type is string.

    now i want to change this value to YYYY-MM-DD format.

    Please help how to chaieve this.

     

    Thanks in advance.

     

     

    Monday, March 31, 2008 8:57 AM

Answers

  • You can convert the value to DATETIME

    Code Snippet

    DECLARE @dt VARCHAR(14)

    SET @dt = '01.12.1998'

    SELECT CONVERT(CONVERT(DATETIME, @dt)

    RESULTS

    1998-01-12 00:00:00.000
     
    DECLARE @dt VARCHAR(14)

    SET @dt = '01.12.1998'

    SELECT CONVERT(CHAR(10),CONVERT(DATETIME, @dt), 20)

    RESULTS

    1998-01-12

    Monday, March 31, 2008 9:17 AM
  •  purush1 wrote:

    Hi,

     

    i have used the above query like this.

     

    SELECT CONVERT(CHAR(10),CONVERT(DATETIME, exp_datet), 20) from table1

     

    but i am getting the following error.

     

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Please help me in this.

     

     

    Did you try this?

    Code Snippet

    SELECT

    CONVERT(CHAR(10),CONVERT(DATETIME, exp_date), 20)

    from table1

    where ISDATE(exp_date) = 1

     

     

    Monday, March 31, 2008 12:45 PM

All replies

  • You can convert the value to DATETIME

    Code Snippet

    DECLARE @dt VARCHAR(14)

    SET @dt = '01.12.1998'

    SELECT CONVERT(CONVERT(DATETIME, @dt)

    RESULTS

    1998-01-12 00:00:00.000
     
    DECLARE @dt VARCHAR(14)

    SET @dt = '01.12.1998'

    SELECT CONVERT(CHAR(10),CONVERT(DATETIME, @dt), 20)

    RESULTS

    1998-01-12

    Monday, March 31, 2008 9:17 AM
  • Hi,

     

    i have used the above query like this.

     

    SELECT CONVERT(CHAR(10),CONVERT(DATETIME, exp_datet), 20) from table1

     

    but i am getting the following error.

     

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Please help me in this.

     

    Monday, March 31, 2008 11:33 AM
  • It sounds as though there are some values in the column that fall outisde of the pattern that ##.##.####.  Run this query to see if all rows meet this pattern.

     

    Code Snippet

    SELECT *

    FROM table1

    WHERE exp_datet NOT LIKE'[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]'

     

     

    Monday, March 31, 2008 11:58 AM
  • That format (20) is longer than 10 characters.

     

    Check out conversion options: http://www.sqlusa.com/bestpractices2005/nocenturydatetimeformat/

     

    or http://www.sqlusa.com/bestpractices2005/centurydateformat/

     

    Monday, March 31, 2008 12:00 PM
  • This should solve it.

     

    DECLARE @dt varchar(10)

    SET @dt = '31.03.2008'

    select Convert(datetime, @dt ,104)

     

    select Convert(varchar(10), Convert(datetime, @dt ,104),126)

     

    Regards,

    Liquidloop

    Monday, March 31, 2008 12:04 PM
  •  purush1 wrote:

    Hi,

     

    i have used the above query like this.

     

    SELECT CONVERT(CHAR(10),CONVERT(DATETIME, exp_datet), 20) from table1

     

    but i am getting the following error.

     

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Please help me in this.

     

     

    Did you try this?

    Code Snippet

    SELECT

    CONVERT(CHAR(10),CONVERT(DATETIME, exp_date), 20)

    from table1

    where ISDATE(exp_date) = 1

     

     

    Monday, March 31, 2008 12:45 PM
  • The CHAR(10) concantenates the value to only show the date portion.

     

    Monday, March 31, 2008 2:52 PM
  • Kalman,

     

    The error message was not about truncation, or the data not fitting in the allocated space, but instead, was about one or more values that would NOT convert to a valid datetime datatype.

     

    Monday, March 31, 2008 3:20 PM
  • Why the column data type is string?
    Why you can't use a datetime or smalldatetime?

    If you need save the date in another type, I recomend int, not varchar. Save a numeric in yyyymmdd format.


    Monday, March 31, 2008 4:12 PM
  • Code Snippet

    DECLARE @Date AS VARCHAR(10)

    SET @Date = '25.12.2008'

    --Option 1 -- this will NOT validate the given date

    SELECT @Date AS 'Date_DD.MM.YYYY', RIGHT(@Date,4)+'-'+SUBSTRING(@Date,4,2)+'-'+LEFT(@Date,2) AS 'Date_YYYY-MM-DD'

    --Option 2  -- this will validate the given date also

    SELECT @Date AS 'Date_DD.MM.YYYY',CONVERT(VARCHAR(10), CONVERT(DATETIME,@Date,104),126) AS 'Date_YYYY-MM-DD'

     

     

    Monday, March 31, 2008 7:42 PM
  • No need to do date validation.  Just manipulate the string.  It's stored as a string, and we know the format you want.  Why bother to convert to date?

     

    Code Snippet

    update <MyTable>

    set <MyField> = right(<MyField>,4) + '-' + substring(<MyField>, 4,2) + '-' + left(<MyField>,2)

     

     

    Monday, March 31, 2008 8:03 PM
  • It should be:

    SELECT CONVERT(CHAR(10),CONVERT(DATETIME, @dt), 120)
     
    Convert format 120 not 20 !
    Friday, September 12, 2008 7:55 AM