none
Date Conversion

    Question

  • I have a date in varchar data type in this format "dd.mm.yyyy" how do i change it to a DATE data type? i want to populate(update) on another field which carries date data types only.


    • Edited by tmpmadula Thursday, July 03, 2014 4:54 AM
    Wednesday, July 02, 2014 1:32 PM

Answers

All replies

  • declare @date varchar(10)='12.12.2014'
    select cast(@date as date)
    --Prashanth
    Wednesday, July 02, 2014 1:50 PM
  • Use the convert function with parameter 104 = German format:

    SELECT CONVERT(date, '30.06.2014', 104)

    See CAST and CONVERT (Transact-SQL) for details


    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    Wednesday, July 02, 2014 1:51 PM
  •   declare @dt varchar(15)
      set @dt = '06.25.2014'
      select CAST(@dt as DATE)


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, July 02, 2014 1:53 PM
  • Convert is MSSQL specific. Cast is the ANSI standard. If you find yourself working in multiple environments frequently, or there is reasonable potential that your code will need to be ported to another dialect of SQL, you should use Cast.
    Wednesday, July 02, 2014 2:38 PM
  •   set @dt = '06.25.2014'
    Ashwin, the requested format is dd.mm.yyyy, in your sample you are using mm.dd.yyyy.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, July 02, 2014 6:30 PM
  • Convert is MSSQL specific. Cast is the ANSI standard.

    Patrick, but the CAST function depends on the language setting of the login; the conversion fails if the language setting is different then German:

    -- This works
    SET LANGUAGE GERMAN;
    SELECT CAST('30.06.2014' AS date);
    GO
    
    -- This fails
    SET LANGUAGE ENGLISH;
    SELECT CAST('30.06.2014' AS date);
    GO
    


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 02, 2014 6:32 PM
  • DECLARE @ds VARCHAR(10)
    SET @ds = '30.06.2014' 
    
    SELECT CAST( RIGHT(@ds,4) + '-'+LEFT(RIGHT(@ds,7),2) +'-'+LEFT(@ds,2) AS date)

    :)

    Yes, + is not ansi standard, but its the only way to concat in mssql.

    Wednesday, July 02, 2014 7:44 PM
  • I want to convert and update that date on another field which is a date data type
    Thursday, July 03, 2014 5:49 AM
  • Then update your data, should be easy:

    UPDATE yourTable
    SET DateColumn = CONVERT(date, StringColumn, 104)
    


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, July 03, 2014 6:05 AM