How to insert DDMMYYYY value in DATE datatype column-CodePudding

I have created table with following structure.

CREATE TABLE TEMP_CUSTOMERS 
(
    CUSTOMER_NAME VARCHAR(255) NOT NULL,
    CUSTOMER_ID VARCHAR(18) NOT NULL,
    CUSTOMER_OPEN_DATE DATE NOT NULL,
    LAST_CUNSULTED_DATE DATE,
    VACCINATION_TYPE CHAR(5),
    DOCTOR_CONSULTED CHAR(5),
    STATE CHAR(5),
    COUNTRY CHAR(5),
    POST_CODE INT,
    DATE_OF_BIRTH DATE,
    ACTIVE_CUSTOMER CHAR(1)
)

I want to insert following values

INSERT INTO TEMP_CUSTOMERS (CUSTOMER_NAME, CUSTOMER_ID,
                            CUSTOMER_OPEN_DATE, LAST_CUNSULTED_DATE,
                            VACCINATION_TYPE, DOCTOR_CONSULTED,
                            STATE, COUNTRY, DATE_OF_BIRTH, ACTIVE_CUSTOMER) 
VALUES ('ALEX', '123457', '20101012', '20121013', 'MVD', 'PAUL', 'SA', 'USA', '06031987', 'A'),
       ('JOHN', '123458', '20101012', '20121013', 'MVD', NULL, 'TN', 'IND', '06031987', 'A'),
       ('MATHEW', '123459', '20101012', '20121013', 'MVD', NULL, 'WAS', 'PHIL', '06031987', 'A'),
       ('MATT', '12345', '20101012', '20121013', 'MVD', NULL, 'BOS', 'NYC', '06031987', 'A'),
       ('JACOB', '1256', '20101012', '20121013', 'MVD', NULL, 'VIC', 'AU', '6031987', 'A')

But I'm getting an error while inserting 06031987 value into DATE_OF_BIRTH column. I tried convert, cast, set dateformat... but no success.

Please help me with this.

CodePudding user response：

For date literals, you should put the year first, then month, then day, ALWAYS. So 06031987 should be 19870306. No exceptions. 1987-03-06 is also permitted, but this still uses year, then month, then day. And for date-only values (with no time portion), the unseparated version is the better choice with Sql Server.

Anything else is still a string, and not a date at all. If you need a date you must convert it manually. This is possible, but it's slower and limited to formats shown in the documentation here:

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Note that none of those formats match your desired DDMMYYYY. The closest you can get is DD/MM/YYYY, which isn't the same thing. This means you're down to parsing out string components into a new value that is compatible with one of those formats.

Finally, for completeness I need to mention the FORMAT() function. This function will let you use the DDMMYYYY format, but it's only for converting dates to strings, not vice versa. It won't help you going the opposite direction.

CodePudding user response：

You can deal with bad formats if you can't change the source, but it's really, really ugly. Here are a couple of ways (there are at least a dozen ways to perform string manipulation and apply style numbers to make SQL Server understand and translate garbage into something you can use):

DECLARE @BadFormat varchar(10) = '06031987';

-- if it's mmddyyyy? June 3rd. Switch to yyyymmdd and use style 112.
SELECT TRY_CONVERT(date, RIGHT(@BadFormat,4)   LEFT(@BadFormat,4), 112);

-- if it's ddmmyyyy? March 6th. Make it dd/mm/yyyy and use style 103.
SELECT TRY_CONVERT(date, STUFF(STUFF(@BadFormat, 5,0,'/'),3,0,'/'), 103);

Example db<>fiddle

But please, please, PLEASE try to fix the source to send you proper, unambiguous, non-regional formats. As Joel suggests, this is YYYYMMDD and YYYYMMDD only. YYYY-MM-DD seems safe, but it's not:

SET LANGUAGE Français;
SELECT CONVERT(datetime, '1987-06-13');

Result (not only in French but actually in over half of the languages SQL Server supports):

Msg 242, Level 16, State 3
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

For a lot more background, see:

CodePudding user response：

You can use DATEFROMPARTS to generate date value from the literal as given below:

declare @datevalue CHAR(8) = '06031987'

select DATEFROMPARTS(RIGHT(@datevalue,4),SUBSTRING(@datevalue,3,2),LEFT(@datevalue,2))

1987-03-06

CodePudding user response：

You most to convert the Italian or Spanish date "06031987" to a valid SQL date type format. The default is YYY-MM-DD, so you can implement a substring function to decompose this value '06031987'

SubString( @Date, 5, 4 )   '-'   SubString( @Date, 3, 2 )  '-'   SubString( @Date, 1, 2 )

Or make an user function like this.

Create Function dbo.fnConvertDMYtoSQLDate ( @Date VarChar( 8 ) )
Returns VarChar( 10 )
As
 Begin
  Declare @dt VarChar( 10 )
  Set @dt = SubString( @Date, 5, 4 )  
            '-'  
            SubString( @Date, 3, 2 )  
            '-'  
            SubString( @Date, 1, 2 )            
  Return @Dt  
 End
go

And call it so. Select dbo.fnConvertDMYtoSQLDate('06031987'). In the insert you can use it so:

INSERT INTO TEMP_CUSTOMERS (CUSTOMER_NAME, CUSTOMER_ID,
                            CUSTOMER_OPEN_DATE, LAST_CUNSULTED_DATE,
                            VACCINATION_TYPE, DOCTOR_CONSULTED,
                            STATE, COUNTRY, DATE_OF_BIRTH, ACTIVE_CUSTOMER)
 VALUES('ALEX', '123457', '20101012', '20121013', 'MVD', 'PAUL', 'SA', 'USA', dbo.fnConvertDMYtoSQLDate('06031987'), 'A')

CodePudding user response：

Try this:

DECLARE @DateString varchar(10) = '06031987';
SELECT REPLACE(CONVERT(date,STUFF(STUFF(@DateString,3,0,'/'),6,0,'/')),'-', '')