Add Date and Serial Time

[calandrelli]

I have an table linked into my SQL Server 2000 database. The linked table contains manufacturing transactions from our mainframe. One field JOB_ACTY_DT is a date field and another field JOB_ACTY_TM is a vchar field that contains a time in 24 hour serial format. I want to create a view that combines these two fields into one date time value named JOB_DATE_T.

For example....

JOB_ACTY_DT JOB_ACT_TM JOB_DATE_T
------------------------------------------------------
11/22/1992 12:00 am 1135 11/22/1992 11:35 am
11/22/1992 12:00 am 835 11/22/1992 08:35 am
11/22/1992 12:00 am 1315 11/22/1992 01:15 pm

I have managed to do it using the following script but this is a large table and this script takes a long time to execute. Can anyone think of a different approach?

CREATE View calancs.qry_IDEA_Job_Linked as
Select DateAdd(hh,(Cast(Left(JOB_ACTY_TM,Len(JOB_ACTY_TM)-2)as Int)), DateAdd(n,Cast(Right(JOB_ACTY_TM,2)as Int),JOB_ACTY_DT)) Job_Date_T,
From OPENQUERY(addiw1, 'select *from IDEA.AMM_JOB_ACTY ')

 

[hneal98]

I have a fealing it is actually slow because of the openquery statement. What you have seems to be fine to me, but others may have a different opinion.

By the way, if you have a time of 08:08, will it show in the JOB_ACTY_TM as 88, or as 808? If it shows as 88, your code will return 00:00.

 

[calandrelli]

8:08 am will be recorded as 808. Thank heaven for that bit of formating or this would be a whole lot harder.

 

[SQLBill]

First, I wonder why you have two separate columns for this information. Wouldn't it be easier to have the actual time as part of the JOB_ACTY_DT value?

Second, there might be another way to do this that might be a better solution for you. Create a new column on the table using the COMPUTED COLUMN. Something like this might work:

ALTER TABLE tablename ADD JOB_DATE_T AS (DateAdd(hh,(Cast(Left(JOB_ACTY_TM,Len(JOB_ACTY_TM)-2)as Int)), DateAdd(n,Cast(Right(JOB_ACTY_TM,2)as Int),JOB_ACTY_DT)))

Disclaimer: I have used computed columns, but I have not tested this type of a computed column.

-SQLBill

Posting advice: FAQ481-4875

 

[calandrelli]

I ultimately don't want two columns… I just want one. The problem I have is that this data is coming from an Oracle based data warehouse that I only have read access to. I want to supplement the records in my database with information from the Oracle database. The warehouse data changes every day and is very large (millions of records) so I don’t want to bring it into my SQL database as a table because of the size. Therefore, I am creating a view of the liked Oracle database and updating my database each morning with the information I need using a stored procedure that uses a LEFT OUTER join to this view. The Oracle warehouse stores the data in this separated data and time format and I would like to find an efficient way to evaluate it in the view as one calculated value.