How to convert the below text string into a date type using SQL Server 2016

up vote
-2
down vote

favorite

ActivateDate        ShipDate                Month   Month_Length    Day-2c  Day-2c_Length   YEAR-201x   SHIPDateConcatenate     ActivateDateConcatenate

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

10/12/2018 14:45    10/16/2018 12:00:00 AM  10      2               16      2               2018        10-16-2018              10-12-2018

The two columns [ActivateDate] and [ShipDate] datatype is listed below; however each time I try to use convert() or cast() to a date type, a conversion error occurs.

SELECT

    [ActivateDate], -- '10/12/2018 14:45' nvarchar(100)

    [ShipDate], -- '6/12/2018 12:00:00 AM'   nvarchar(100)

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2) as 'Month',

    LEN(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)) as 'Month_Length',

    REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') as 'Day-2c',

    LEN(REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','')) as 'Day-2c_Length',

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'YEAR-201x',

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)

+'-'+REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') 

+'-'+SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'SHIPDateConcatenate',

    SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])-2,2)

+'-'+REPLACE(SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])+1,2),'/','') 

+'-'+SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/201', iedimpr.[ActivateDate])+1,4) as 'ActivateDateConcatenate'

edited Nov 12 at 13:32

Zohar Peled

51.3k73172

asked Nov 11 at 4:17

TMilzSr

2

6/12/2018 - Is it December 6th or Jun 12th? Same question for 6-12-2018. Also, why are you storing dates as strings? Store dates as date and dates + times as datetime2.
– Zohar Peled
Nov 11 at 8:20

Good Morning, That is my point. The original data is nvarchar(100) and want to convert it into a date. I initially tried convert(date,[shipdate]) and convert(nvarchar(10),[shipdate],102) but neither seemed to work. In the interium, I tried to at least separate the number as a workaround to convert to a date.
– TMilzSr
Nov 12 at 11:58

add a comment |

up vote
-2
down vote

favorite

ActivateDate        ShipDate                Month   Month_Length    Day-2c  Day-2c_Length   YEAR-201x   SHIPDateConcatenate     ActivateDateConcatenate

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

10/12/2018 14:45    10/16/2018 12:00:00 AM  10      2               16      2               2018        10-16-2018              10-12-2018

The two columns [ActivateDate] and [ShipDate] datatype is listed below; however each time I try to use convert() or cast() to a date type, a conversion error occurs.

SELECT

    [ActivateDate], -- '10/12/2018 14:45' nvarchar(100)

    [ShipDate], -- '6/12/2018 12:00:00 AM'   nvarchar(100)

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2) as 'Month',

    LEN(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)) as 'Month_Length',

    REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') as 'Day-2c',

    LEN(REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','')) as 'Day-2c_Length',

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'YEAR-201x',

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)

+'-'+REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') 

+'-'+SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'SHIPDateConcatenate',

    SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])-2,2)

+'-'+REPLACE(SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])+1,2),'/','') 

+'-'+SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/201', iedimpr.[ActivateDate])+1,4) as 'ActivateDateConcatenate'

edited Nov 12 at 13:32

Zohar Peled

51.3k73172

asked Nov 11 at 4:17

TMilzSr

2

6/12/2018 - Is it December 6th or Jun 12th? Same question for 6-12-2018. Also, why are you storing dates as strings? Store dates as date and dates + times as datetime2.
– Zohar Peled
Nov 11 at 8:20

Good Morning, That is my point. The original data is nvarchar(100) and want to convert it into a date. I initially tried convert(date,[shipdate]) and convert(nvarchar(10),[shipdate],102) but neither seemed to work. In the interium, I tried to at least separate the number as a workaround to convert to a date.
– TMilzSr
Nov 12 at 11:58

add a comment |

up vote
-2
down vote

favorite

ActivateDate        ShipDate                Month   Month_Length    Day-2c  Day-2c_Length   YEAR-201x   SHIPDateConcatenate     ActivateDateConcatenate

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

10/12/2018 14:45    10/16/2018 12:00:00 AM  10      2               16      2               2018        10-16-2018              10-12-2018

The two columns [ActivateDate] and [ShipDate] datatype is listed below; however each time I try to use convert() or cast() to a date type, a conversion error occurs.

SELECT

    [ActivateDate], -- '10/12/2018 14:45' nvarchar(100)

    [ShipDate], -- '6/12/2018 12:00:00 AM'   nvarchar(100)

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2) as 'Month',

    LEN(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)) as 'Month_Length',

    REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') as 'Day-2c',

    LEN(REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','')) as 'Day-2c_Length',

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'YEAR-201x',

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)

+'-'+REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') 

+'-'+SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'SHIPDateConcatenate',

    SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])-2,2)

+'-'+REPLACE(SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])+1,2),'/','') 

+'-'+SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/201', iedimpr.[ActivateDate])+1,4) as 'ActivateDateConcatenate'

edited Nov 12 at 13:32

Zohar Peled

51.3k73172

asked Nov 11 at 4:17

TMilzSr

ActivateDate        ShipDate                Month   Month_Length    Day-2c  Day-2c_Length   YEAR-201x   SHIPDateConcatenate     ActivateDateConcatenate

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

NULL                6/12/2018 12:00:00 AM   6       1               12      2               2018        6-12-2018               NULL

10/12/2018 14:45    10/16/2018 12:00:00 AM  10      2               16      2               2018        10-16-2018              10-12-2018

The two columns [ActivateDate] and [ShipDate] datatype is listed below; however each time I try to use convert() or cast() to a date type, a conversion error occurs.

SELECT

    [ActivateDate], -- '10/12/2018 14:45' nvarchar(100)

    [ShipDate], -- '6/12/2018 12:00:00 AM'   nvarchar(100)

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2) as 'Month',

    LEN(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)) as 'Month_Length',

    REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') as 'Day-2c',

    LEN(REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','')) as 'Day-2c_Length',

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'YEAR-201x',

    SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)

+'-'+REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') 

+'-'+SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'SHIPDateConcatenate',

    SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])-2,2)

+'-'+REPLACE(SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])+1,2),'/','') 

+'-'+SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/201', iedimpr.[ActivateDate])+1,4) as 'ActivateDateConcatenate'

sql string date sql-server-2016

edited Nov 12 at 13:32

Zohar Peled

51.3k73172

asked Nov 11 at 4:17

TMilzSr

edited Nov 12 at 13:32

Zohar Peled

51.3k73172

asked Nov 11 at 4:17

TMilzSr

edited Nov 12 at 13:32

Zohar Peled

51.3k73172

edited Nov 12 at 13:32

Zohar Peled

51.3k73172

edited Nov 12 at 13:32

Zohar Peled

51.3k73172

asked Nov 11 at 4:17

TMilzSr

asked Nov 11 at 4:17

TMilzSr

asked Nov 11 at 4:17

TMilzSr

2

6/12/2018 - Is it December 6th or Jun 12th? Same question for 6-12-2018. Also, why are you storing dates as strings? Store dates as date and dates + times as datetime2.
– Zohar Peled
Nov 11 at 8:20

Good Morning, That is my point. The original data is nvarchar(100) and want to convert it into a date. I initially tried convert(date,[shipdate]) and convert(nvarchar(10),[shipdate],102) but neither seemed to work. In the interium, I tried to at least separate the number as a workaround to convert to a date.
– TMilzSr
Nov 12 at 11:58

add a comment |

2

6/12/2018 - Is it December 6th or Jun 12th? Same question for 6-12-2018. Also, why are you storing dates as strings? Store dates as date and dates + times as datetime2.
– Zohar Peled
Nov 11 at 8:20

Good Morning, That is my point. The original data is nvarchar(100) and want to convert it into a date. I initially tried convert(date,[shipdate]) and convert(nvarchar(10),[shipdate],102) but neither seemed to work. In the interium, I tried to at least separate the number as a workaround to convert to a date.
– TMilzSr
Nov 12 at 11:58

6/12/2018 - Is it December 6th or Jun 12th? Same question for 6-12-2018. Also, why are you storing dates as strings? Store dates as date and dates + times as datetime2.
– Zohar Peled
Nov 11 at 8:20

Good Morning, That is my point. The original data is nvarchar(100) and want to convert it into a date. I initially tried convert(date,[shipdate]) and convert(nvarchar(10),[shipdate],102) but neither seemed to work. In the interium, I tried to at least separate the number as a workaround to convert to a date.
– TMilzSr
Nov 12 at 11:58

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

After reading the question again I've noticed the sample data is ill-formatted, so I've edited it. During this edit I found that you are using american style (mm/dd/yyyy) for your string representation of dates.

To convert a valid string representation of DateTime with this format into date, you need to use 101 as the style parameter in the convert method.

However, I highly recommend using Try_convert instead of Convert, since it will simply return null when the value can't be converted instead of raising an error.

That being said, here is an example:

First, create and populate sample data(Please save us this step in your future questions):

DECLARE @T AS TABLE

(

    ActivateDate nvarchar(100),

    ShipDate nvarchar(100)

)



INSERT INTO @T(ActivateDate, ShipDate) VALUES

(NULL,                  '6/12/2018 12:00:00 AM'),

('10/12/2018 14:45',    '10/16/2018 12:00:00 AM'),

('20/14/2018 14:45',    '10/16/2018 12:00:00 AM'), -- invalid ActivateDate

('2/4/2018 14:45',      '10/16/ZOIB 12:00:00 AM') -- invalid ShipDate

The query:

SELECT  ActivateDate, 

        ShipDate,

        TRY_CONVERT(datetime, ActivateDate, 101) As DateActiveDate,

        TRY_CONVERT(datetime, ShipDate, 101) As DateShipDate

FROM @T

Results:

ActivateDate        ShipDate                    DateActiveDate          DateShipDate

NULL                6/12/2018 12:00:00 AM       NULL                    12.06.2018 00:00:00

10/12/2018 14:45    10/16/2018 12:00:00 AM      12.10.2018 14:45:00     16.10.2018 00:00:00

20/14/2018 14:45    10/16/2018 12:00:00 AM      NULL                    16.10.2018 00:00:00

2/4/2018 14:45      10/16/ZOIB 12:00:00 AM      04.02.2018 14:45:00     NULL

If you only need Date (without the time part), simply use try_convert(date, ...) instead of try_convert(datetime, ...).

answered Nov 12 at 13:54

Zohar Peled

51.3k73172

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53245790%2fhow-to-convert-the-below-text-string-into-a-date-type-using-sql-server-2016%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

That being said, here is an example:

First, create and populate sample data(Please save us this step in your future questions):

DECLARE @T AS TABLE

(

    ActivateDate nvarchar(100),

    ShipDate nvarchar(100)

)



INSERT INTO @T(ActivateDate, ShipDate) VALUES

(NULL,                  '6/12/2018 12:00:00 AM'),

('10/12/2018 14:45',    '10/16/2018 12:00:00 AM'),

('20/14/2018 14:45',    '10/16/2018 12:00:00 AM'), -- invalid ActivateDate

('2/4/2018 14:45',      '10/16/ZOIB 12:00:00 AM') -- invalid ShipDate

The query:

SELECT  ActivateDate, 

        ShipDate,

        TRY_CONVERT(datetime, ActivateDate, 101) As DateActiveDate,

        TRY_CONVERT(datetime, ShipDate, 101) As DateShipDate

FROM @T

Results:

ActivateDate        ShipDate                    DateActiveDate          DateShipDate

NULL                6/12/2018 12:00:00 AM       NULL                    12.06.2018 00:00:00

10/12/2018 14:45    10/16/2018 12:00:00 AM      12.10.2018 14:45:00     16.10.2018 00:00:00

20/14/2018 14:45    10/16/2018 12:00:00 AM      NULL                    16.10.2018 00:00:00

2/4/2018 14:45      10/16/ZOIB 12:00:00 AM      04.02.2018 14:45:00     NULL

If you only need Date (without the time part), simply use try_convert(date, ...) instead of try_convert(datetime, ...).

answered Nov 12 at 13:54

Zohar Peled

51.3k73172

add a comment |

up vote
0
down vote

That being said, here is an example:

First, create and populate sample data(Please save us this step in your future questions):

DECLARE @T AS TABLE

(

    ActivateDate nvarchar(100),

    ShipDate nvarchar(100)

)



INSERT INTO @T(ActivateDate, ShipDate) VALUES

(NULL,                  '6/12/2018 12:00:00 AM'),

('10/12/2018 14:45',    '10/16/2018 12:00:00 AM'),

('20/14/2018 14:45',    '10/16/2018 12:00:00 AM'), -- invalid ActivateDate

('2/4/2018 14:45',      '10/16/ZOIB 12:00:00 AM') -- invalid ShipDate

The query:

SELECT  ActivateDate, 

        ShipDate,

        TRY_CONVERT(datetime, ActivateDate, 101) As DateActiveDate,

        TRY_CONVERT(datetime, ShipDate, 101) As DateShipDate

FROM @T

Results:

ActivateDate        ShipDate                    DateActiveDate          DateShipDate

NULL                6/12/2018 12:00:00 AM       NULL                    12.06.2018 00:00:00

10/12/2018 14:45    10/16/2018 12:00:00 AM      12.10.2018 14:45:00     16.10.2018 00:00:00

20/14/2018 14:45    10/16/2018 12:00:00 AM      NULL                    16.10.2018 00:00:00

2/4/2018 14:45      10/16/ZOIB 12:00:00 AM      04.02.2018 14:45:00     NULL

If you only need Date (without the time part), simply use try_convert(date, ...) instead of try_convert(datetime, ...).

answered Nov 12 at 13:54

Zohar Peled

51.3k73172

add a comment |

up vote
0
down vote

That being said, here is an example:

First, create and populate sample data(Please save us this step in your future questions):

DECLARE @T AS TABLE

(

    ActivateDate nvarchar(100),

    ShipDate nvarchar(100)

)



INSERT INTO @T(ActivateDate, ShipDate) VALUES

(NULL,                  '6/12/2018 12:00:00 AM'),

('10/12/2018 14:45',    '10/16/2018 12:00:00 AM'),

('20/14/2018 14:45',    '10/16/2018 12:00:00 AM'), -- invalid ActivateDate

('2/4/2018 14:45',      '10/16/ZOIB 12:00:00 AM') -- invalid ShipDate

The query:

SELECT  ActivateDate, 

        ShipDate,

        TRY_CONVERT(datetime, ActivateDate, 101) As DateActiveDate,

        TRY_CONVERT(datetime, ShipDate, 101) As DateShipDate

FROM @T

Results:

ActivateDate        ShipDate                    DateActiveDate          DateShipDate

NULL                6/12/2018 12:00:00 AM       NULL                    12.06.2018 00:00:00

10/12/2018 14:45    10/16/2018 12:00:00 AM      12.10.2018 14:45:00     16.10.2018 00:00:00

20/14/2018 14:45    10/16/2018 12:00:00 AM      NULL                    16.10.2018 00:00:00

2/4/2018 14:45      10/16/ZOIB 12:00:00 AM      04.02.2018 14:45:00     NULL

If you only need Date (without the time part), simply use try_convert(date, ...) instead of try_convert(datetime, ...).

answered Nov 12 at 13:54

Zohar Peled

51.3k73172

That being said, here is an example:

First, create and populate sample data(Please save us this step in your future questions):

DECLARE @T AS TABLE

(

    ActivateDate nvarchar(100),

    ShipDate nvarchar(100)

)



INSERT INTO @T(ActivateDate, ShipDate) VALUES

(NULL,                  '6/12/2018 12:00:00 AM'),

('10/12/2018 14:45',    '10/16/2018 12:00:00 AM'),

('20/14/2018 14:45',    '10/16/2018 12:00:00 AM'), -- invalid ActivateDate

('2/4/2018 14:45',      '10/16/ZOIB 12:00:00 AM') -- invalid ShipDate

The query:

SELECT  ActivateDate, 

        ShipDate,

        TRY_CONVERT(datetime, ActivateDate, 101) As DateActiveDate,

        TRY_CONVERT(datetime, ShipDate, 101) As DateShipDate

FROM @T

Results:

ActivateDate        ShipDate                    DateActiveDate          DateShipDate

NULL                6/12/2018 12:00:00 AM       NULL                    12.06.2018 00:00:00

10/12/2018 14:45    10/16/2018 12:00:00 AM      12.10.2018 14:45:00     16.10.2018 00:00:00

20/14/2018 14:45    10/16/2018 12:00:00 AM      NULL                    16.10.2018 00:00:00

2/4/2018 14:45      10/16/ZOIB 12:00:00 AM      04.02.2018 14:45:00     NULL

If you only need Date (without the time part), simply use try_convert(date, ...) instead of try_convert(datetime, ...).

answered Nov 12 at 13:54

Zohar Peled

51.3k73172

answered Nov 12 at 13:54

Zohar Peled

51.3k73172

answered Nov 12 at 13:54

Zohar Peled

51.3k73172

answered Nov 12 at 13:54

Zohar Peled

51.3k73172

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky