asked on

converting char to datetime

Hi experts,

I'm importing a text file that has been generated from vsam to ms sql by DTS ---working fine.
what i did is i created two tables and actually they are the same but the first table with varchar data types and the other with a specific type just to solve the problem because what i get from the text file is wrong format, so i have a column in the text file contains data for time and the format is like 01200

What i did in the first table is

update table set column = substring(column,2,4);
update table set column = substring(column,1,2)+':'+substring(column,3,2);

and it will be like 12:00

then i transferred the first table to the other table with data type of datatime,the error that appears says" the conversion of a char data type to a datetime type resulted in an out-of-range datetime value",but if the data type of the table2 was varchar it will successfully imports.

My question is can i now which row the error stops on or is there a log file for this DTS because the text file contains around 5000 record, and the important thing is after it will be imported can i make a formula column to subtract this column with another column with same type .

For example 12:00 - 08:00 = 4
Thanks in advance

ASKER CERTIFIED SOLUTION

Ramesh Srinivas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bduhaish

ASKER

By DTS,but the flat file has around 5000 record and when i transfer 100 or more but less than 5000 it woks so i think there is something wrong ,do you suggest a technical way to check.

bduhaish

ASKER

saleek ,

thanks,

Problem been solved,
like you said I found that the some values contains 0000 so I updated these value to 1111 then the job was successfully imported.
about subtracting times it's working fine but what about if I have minutes like 10:30 – 07:55
What should be change for the function
datediff(hour,time1,time2) as times

Ramesh Srinivas

If you want to find out the difference as -3 Hours and 25 Minutes then you could do it like so - copy and paste the following into sql analyzer:

select Convert(int, DATEDIFF(Hour, '10:30', '07:55')) as Hours,
DATEDIFF(Minute, ( DATEADD(Hour, Convert(int, DATEDIFF(Hour, '10:30', '07:55')), '10:30') ), '07:55') as Minutes

Also, if you wish to convert the hours to a positive number then just multiply by 1.

regards,

KS

bduhaish

ASKER

saleek ,

Sorry for been late, I paste the function as you said and it was working good but I triad to concatenate the two functions together in one alis column but i get error

select Convert(int, DATEDIFF(Hour, '10:30', '07:55')) +
DATEDIFF(Minute, ( DATEADD(Hour, Convert(int, DATEDIFF(Hour, '10:30', '07:55')), '10:30') ), '07:55') as Minutes
This one ignores hours and give minutes ???

select Convert(int, DATEDIFF(Hour, '10:30', '07:55')) as Hours,
DATEDIFF(Minute, ( DATEADD(Hour, Convert(int, DATEDIFF(Hour, '10:30', '07:55')), '10:30') ), '07:55') as Minutes,
Hours+':'+Minutes as Time

This give s me error??

bduhaish

ASKER

saleek,
wake up

bduhaish

ASKER

saleek,
Something new came up related to this question, first I have to close this question and post a new one but I hope that you answer my last comment and I hope I’ll see you there.
Thanks….

Ramesh Srinivas

Hi,

Sorry, have been away.

If you wish to concantenate the 2 answers together then convert them to strings and remove blanks like so:

select RTRIM(str(Convert(int, DATEDIFF(Hour, '10:30', '07:55')))) + ' ' +
LTRIM(str(DATEDIFF(Minute, ( DATEADD(Hour, Convert(int, DATEDIFF(Hour, '10:30', '07:55')), '10:30') ), '07:55'))) as HoursMinutes

This will give you....

-3 25

And remember if you do not want the minus then just multiply the hours by 1.

regards,

KS