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.

  • Datatype for System.Version in sql server
  • type of loops in sql server?
  • SQL Server - Guid VS. Long
  • Query to pick values based on latest date in each group. Default to greatest ID if same date
  • SQL Server stored procedure with optional parameters updates wrong columns
  • Alter Table Add Column Syntax
  • 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

    Thanks

    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)) 
    RETURNS DATETIME
    AS
    BEGIN
    
        RETURN CONVERT(DATETIME, RIGHT(@customDateString, 4) + RIGHT(LEFT(@customDateString, 4), 2) + LEFT(@customDateString, 2))
    
    END

    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.