nvarchar data type to a datetime SQL query

Question

I have 2 tables ServiceType AND Services.

Service table structure

    ServiceID          int           Unchecked
    Service_TypeID     int           Unchecked
    Last_performed     date          Unchecked
    Frequency          nvarchar(50)  Unchecked
    Freq_Duration      nvarchar(50)  Unchecked
    Freq_Mileage       nvarchar(50)  Unchecked
    Remin_frequ        nvarchar(50)  Unchecked
    Remin_duration     nvarchar(50)  Unchecked
    Remin_Mileage      nvarchar(50)  Checked
    Next_due_datetime  datetime      Unchecked
    Next_due_mileage   nvarchar(50)  Unchecked

ServiceType structure is

Service_TypeID  int             Unchecked
Service_Type    nvarchar(50)    Unchecked

Sample data

ServiceID   Service_TypeID      Last_performed  Frequency   Freq_Duration   Freq_Mileage    Remin_frequ Remin_duration  Remin_Mileage   Next_due_datetime   Next_due_mileage
3             2                 2012-12-01      6             month(s)       1000              1          day(s)          10            2013-03-10              1000

and I want data like this:

ServiceID   Service_TypeID      Last_performed  Service Frequency    Next Service      Create Reminder 
3             2                 2012-12-01      6 month(s)  1000     2013-03-10 1000    1 day(s) 10

For this I try this query:

select  
    Services.ServiceID,
    ServiceType.Service_Type,
    Last_performed
    Services.Frequency+''+Freq_Duration+''+Freq_Mileage as [Service Frequency],
    Services.Next_due_datetime+''+Next_due_mileage as [Next Service],
    Services.Remin_frequ+''+Remin_duration+''+Remin_Mileage as [Create Reminder]
from 
    Services
inner join 
    ServiceType on ServiceType.Service_TypeID = Services.Service_TypeID

but I get this error:

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

Answer 1

Try using concat

 concat(Services.Frequency,' ',Freq_Duration+' ',Freq_Mileage) as [Service Frequency]

Else need to use cast or convert to varchar for integer