Convert String Value into Datetime in SQL

Question

I have a problem with my query

USE [db]
GO

SELECT 
    TD.ID, LOM.ScanDate, RD.ProcessorType, RD.ProcessorResult, RD.Score  
FROM 
    [dbo].[LogicalObject_Metadatas] LOM WITH(NOLOCK)
JOIN 
    [dbo].[LogicalObject] LO WITH(NOLOCK) ON LO.ID = LOM.OBJECTID
JOIN 
    [db2].[dbo].[JobData] JD With(NOLOCK) ON LO.DOCUMENTID = JD.DocumentID
JOIN 
    [db2].[dbo].[TaskData] TD WITH(NOLOCK) ON JD.ID = TD.ParentID
JOIN 
    [db2].[dbo].[RecognitionData] RD WITH(NOLOCK) ON TD.ID = RD.ID
WHERE
    LOM.[Source] = 'MANUFAST_AVR_DNN_QA'
    AND LOM.[ScanDate] > '2018-10-01'
    AND LOM.[ScanDate] < '2018-10-16'
    AND RD.[ProcessorType] IN ('F05AM', 'F06', 'F08', 'F09', 'F11', 'F12A')

I want to convert the [ProcessorResult] into datetime if the [ProcessorType] is = F05AM; if not, to be the way it is.

I have tried to use IF, cast but no result.

This is the way query is looking :

enter image description here

There are other values in ProcessorResult that match the search criteria that cannot be converted to a datetime - "3" for example. As you cannot have mixed types you would need to reformat the 6 character dates into whatever string format you need. — Alex K.
– Alex K., Commented Oct 17, 2018 at 14:45

Gordon Linoff · Accepted Answer · 2018-10-17 15:19:59Z

1

The most important point is that you cannot have a column that is sometimes a date/time and sometimes not. Columns have fixed types.

You can convert the string into a date with some string manipulation. This creates a date column:

select (case when ProcessorType = 'F05AM'
             then convert(date, '20' + right(ProcessorResult, 2) + substring(ProcessorResult, 3, 2) + left(ProcessorResult, 2))
        end)

You just want a string that looks like a date, so I would do:

select (case when ProcessorType = 'F05AM'
             then '20' + right(ProcessorResult, 2) + substring(ProcessorResult, 3, 2) + left(ProcessorResult, 2)
             else ProcessorResult
        end)

edited Oct 17, 2018 at 15:19

answered Oct 17, 2018 at 15:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

3N1GM4 Over a year ago

Is case when ProcessorType is = 'F05AM' valid syntax? I have assumed MSSQL based on what appear to be SSMS in the question, and so would expect case when ProcessorType = 'F05AM'...

Gordon Linoff Over a year ago

@3N1GM4 . . . No it is not. Thank you.

claudiu lujanschi · Accepted Answer · 2019-10-16 06:11:00Z

You can use a case statement in your select clause to achieve this, along with some stuff calls to handle your input format of DDMMYY (replace the 120 style with any other one if you prefer):

select 
    TD.ID,
    LOM.ScanDate,
    RD.ProcessorType,
    case 
        when RD.ProcessorType = 'F05AM' and ISDATE(RD.ProcessorResult) = 1
        then convert(varchar,convert(datetime,stuff(stuff(RD.ProcessorResult,5,0,'.'),3,0,'.'), 4),120)  
        else RD.ProcessorResult 
    end as ProcessorResult,
    RD.Score  
from [dbo].[LogicalObject_Metadatas] LOM with (nolock)
join [dbo].[LogicalObject] LO with(nolock) on LO.ID = LOM.OBJECTID
join [db2].[dbo].[JobData] JD with(nolock) on LO.DOCUMENTID = JD.DocumentID
join [db2].[dbo].[TaskData] TD with(nolock) on JD.ID = TD.ParentID
join [db2].[dbo].[RecognitionData] RD with(nolock) on TD.ID = RD.ID
where LOM.[Source] = 'MANUFAST_AVR_DNN_QA'
and LOM.[ScanDate] > '2018-10-01'
and LOM.[ScanDate] < '2018-10-16'
and RD.[ProcessorType] IN ('F05AM','F06','F08','F09','F11','F12A')

It's probably not advisable to have a mixture of data types in the same column though, as you won't be able to do much of any use with these dates in the way of aggregation or analysis while there are non-date values mixed in there.

Collectives™ on Stack Overflow

Convert String Value into Datetime in SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related