Datetime conversion

Question

Hi

Thanks all who helped me for bulk insert.

Now I have a problem with date conversion for another bulk insert

Below is the error

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

I have 2 column in my text file one for date and another for time and I am when combine and convert it says error

 select CAST(DateOfService] AS DATETIME) + CAST ([TimeOfService] AS DATETIME)AS [DOS]

From logtable

My data in the text file is 12/11/2013";"134500

Date of service and timeof service are columns in the text file.

I want it to be displayed as 2013-11-12 13:45:00 and the column name should be DOS

Can sombody help me in doing this with syntax.

Thanks

Mary Abrham

Answer 1

Mary,

I can only assume that the ";" value is either a part of 'DateOfService' or 'TimeOfService'. Based on the above my guess is that something like this would do if that is the case. I hardcoded the date/time fields to show the example.

select cast(replace('12/11/2013;',';','')asdatetime)+cast('13:45:00'asdatetime)

In some fashion a char value must be in your value set that will not allow the conversion to work appropriately.

If the above can't be directly applied, my guess is that some sort of variaton to remove the errant char value would work.

Answer 2

create table  logtable (DateOfService  varchar(20)  , [TimeOfService]  varchar(20))
Insert into logtable values('12/11/2013','134500')

select  CAST(DateOfService + ' ' + Left([TimeOfService],2)+':'+Substring([TimeOfService],3,2)+':'+Right([TimeOfService],2) AS DATETIME) AS [DOS] 

From logtable

drop table logtable

Answer 3

Hi Thanks  

It worked