• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

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




0
bduhaish
Asked:
bduhaish
  • 5
  • 3
1 Solution
 
Ramesh SrinivasTechnical ConsultantCommented:
How are you transferring the data? You could run some simple queries to check the length of values in the column and also if it contains any irregular characters.

As for subtracting times, you would have to use DATEDIFF() function i think.

select DATEDIFF(Hour, '12:00', '08:00') as HourDifference

The above would give you -4.

0
 
bduhaishAuthor Commented:
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.
0
 
bduhaishAuthor Commented:
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  
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Ramesh SrinivasTechnical ConsultantCommented:
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
0
 
bduhaishAuthor Commented:
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??
0
 
bduhaishAuthor Commented:
saleek,
wake up
0
 
bduhaishAuthor Commented:
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….

0
 
Ramesh SrinivasTechnical ConsultantCommented:
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now