Error Converting Datetime from Character String

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error Converting Datetime from Character String

Author

Topic

griffcj
Starting Member

9 Posts

Posted - 2004-04-30 : 09:44:29

I am importing a text file from our phone system at work and I am trying to get it into my table in SQL Server to be able to use for billing purposes. I had it working for awhile with a work around that no longer works and I am trying to fix it for good.

Here is what is going on. I import the file into a table. That works fine, but the date field to be able to import correctly is actually a Char(13) field. This table is a table that I use as a temp type table. It only moves the new phone calls over into the "production" table. I cannot seem to get the "Char" field into a "DateTime" field. Here is the sytax that I have tried and a sample record.

***********

SELECT junk,
time_stamp = CASE
WHEN RIGHT(RTRIM(time_stamp), 1) = 'A' THEN CAST(RTRIM(CONVERT(Char(11), time_stamp, 101) + ' AM') AS DATETIME)
WHEN RIGHT(RTRIM(time_stamp), 1) = 'P' THEN CAST(RTRIM(CONVERT(Char(11), time_stamp, 101) + ' PM') AS DATETIME)
END,
call_length, ext_out, Col005, num_out, Col007, ext_in, account_code, caller_id, ext_speed
FROM a_temp

***************

% 01/05 03:27P 00:29:44 T102 000 71040 552 203 100042 9043630908 71040

**************

Any help or direction would be great, as this problem is holding up billing procedures for my office.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-30 : 09:51:31

You will probably need to add a Year to the date string.

what I am saying is....

select CAST('01/05/2004 03:27' as datetime)
That works because there is a year added there.

Duane.

griffcj
Starting Member

9 Posts

Posted - 2004-04-30 : 10:00:21

This is the format that I get if i use the following syntax:

time_stamp = CASE
WHEN RIGHT(RTRIM(time_stamp), 1) = 'A' THEN CONVERT(Char(11), time_stamp, 101) + ' AM'
WHEN RIGHT(RTRIM(time_stamp), 1) = 'P' THEN CONVERT(Char(11), time_stamp, 101) + ' PM'
END,

-----
01/05 03:21 PM
-----

How can I add the year?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-30 : 10:32:41

I don't have data to test with,
But this might do it:

WHEN RIGHT(RTRIM(time_stamp), 1) = 'A' THEN CONVERT(Char(11), left(time_stamp, 4) + '/2004 ' + right(time_stamp, len(timestamp) - 5), 101) + ' AM'
WHEN RIGHT(RTRIM(time_stamp), 1) = 'P' THEN CONVERT(Char(11), left(time_stamp, 4) + '/2004 ' + right(time_stamp, len(timestamp) - 5), 101) + ' PM'

Duane.

griffcj
Starting Member

9 Posts

Posted - 2004-04-30 : 11:21:39

Thank you Duane. Your a life saver. I got it to work. Here is the syntax that got it working. It gives me the date and the time with the year added in.

WHEN RIGHT(RTRIM(time_stamp), 1) = 'A' THEN CONVERT(Char(16), left(LTRIM(time_stamp), 5) + '/2004 ' + right(time_stamp, len(time_stamp) - 5), 101) + ' AM'
WHEN RIGHT(RTRIM(time_stamp), 1) = 'P' THEN CONVERT(Char(16), left(LTRIM(time_stamp), 5) + '/2004 ' + right(time_stamp, len(time_stamp) - 5), 101) + ' PM'

Thank you again.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-30 : 12:27:28

Glad to be of assistance.

Is today a public holiday in USA? - I am not seeing the regulars here today Like Brett and Tara.

Duane.

griffcj
Starting Member

9 Posts

Posted - 2004-04-30 : 12:54:40

No. Today is a normal work day to my knowledge.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources