ddmmyyyy to sql datetime in SQL

I need to convert a nvarchar value to datetime in T-SQL. The value is in ddmmyyyy format, e.g. 23072009

I need to convert to datetime in T-SQL.

  • Defined SQL behavior when ordering by values with same value
  • How does the SQL Server query optimizer choose non clustered indexes from available indexes for particular table?
  • using > in case statement causes syntax error
  • Better concurrency in Oracle than SQL Server?
  • why we need to use sort transformation before merge join transformation in ssis
  • Case When SQL to LINQ
  • I tried

    select convert(datetime, '23072009', 103)

    But it is throwing error.

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

    Any idea


    5 Solutions collect form web for “ddmmyyyy to sql datetime in SQL”

    Rebuild your format to yyyymmdd.

    declare @D varchar(8)
    set @D = '23072009'
    select cast(right(@D, 4)+substring(@D, 3, 2)+left(@D, 2) as datetime)

    The style 103 will accept strings with dd/mm/yyyy format. So your code should be

    declare @date varchar(8)
    set @date='23072009'
    select convert(datetime,stuff(stuff(@date,5,0,'/'),3,0,'/')  , 103) 

    Format 103 expects dd/mm/yyyy. (see here)

    I don’t think you’ll be able to do it using convert, as non of the convert options have that format.

    You can define custom function like this:

    CREATE FUNCTION [dbo].[GetCustomDate] (@customDateString NVARCHAR(MAX)) 
        RETURN CONVERT(DATETIME, RIGHT(@customDateString, 4) + RIGHT(LEFT(@customDateString, 4), 2) + LEFT(@customDateString, 2))

    You need to cast a string and not a int. Put some quotes:

    convert(datetime, '23072009', 103)

    And 103 gets the string like ‘dd/mm/yyyy’ and not ‘ddmmyyyy’.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.