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.
| Author |
Topic |
|
jpiscit1
Posting Yak Master
130 Posts |
 Posted - 2003-01-07 : 08:58:50
|
| I have run into a scenario that I was wondering if someone could help me out on. We currently have PC's on our production floor that is used to provide interface to the manufacturing operations. These PC's run HMI (Human Machine Interface) software that have the capability to communicate to our SQL servers through specialized database blocks using ODBC. We use these blocks to write numerous data points to the SQL server most which are triggered by our Programmable Logic Controllers (PLC's).My problem is:The software does not provide the means to store datetime information in a datetime format prior to sending the data to SQL. Therefore I end up having to send the datetime information to a table (through a stored Procedure) that has column types defined as varchar. This works fine. However, when I access the data I am forced to convert it prior to doing any data manipulation as you would expect. This process is very time consuming to set up and does not run well on the HMI PC. I was wondering if there is a way in SQL to have the table perhaps write to another table and do this conversion for me. The new table would have all these start values in datetime format. The trick would be updating the 2nd table only when a new record was inserted into the 1st.Maybe there is a simpler way all together. Ultimately I'd like the data in datetime format when I need to access it for manipulation. Any thoughts you would have would be helpful. Thanks,John |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-07 : 09:08:20
|
| If you're using SQL 7.0 or higher, how about using a computed column to convert the varchar data to datetime? It requires no additional storage space, and you can use the existing table. The only downside is that you can't index a computed column in 7.0 (you can in 2000) so you may not improve performance much.Failing that, you can create a view that does the conversion and then use the view for queries, instead of the table itself. |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-01-07 : 09:09:31
|
| Could you set up an insert trigger that does it? Otherwise, I'm not sure why you can't send straight to a table that stores the information as datetimes in SQL. If you are storing as varchars and then converting on the way out, does it not follow that you could convert on the way in and store as a datetime? Can this not be part of the stored procedure?-------Moo. |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2003-01-07 : 10:28:34
|
| Both of these answers are viable. Regarding number 1 answer, I am not familiar with how you would compute a varchar to a datetime. I assume you would use the formula column? If you have an example that would be great! I am using SQL2000.Regarding answer 2, I tried this but I am having difficulty geting the syntax right. This is what I had....CREATE PROCEDURE [ins_3pm6] (@var1 varchar(30))ASInsert into tbl_3PMStartTimes CONVERT(datetime, @var1) values @var1GOI get an error on the 'CONVERT'.John |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-07 : 10:33:02
|
quote:
CREATE PROCEDURE [ins_3pm6] (@var1 varchar(30)) AS Insert into tbl_3PMStartTimes CONVERT(datetime, @var1) values @var1 GO
INSERT INTO tbl_3PMStartTimes VALUES (CONVERT(datetime,@var1))also if you keep you dates in iso format you wont need to use the convert function.Edited by - ValterBorges on 01/07/2003 10:34:26 |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2003-01-07 : 10:47:07
|
| Very nice! This is what I was looking for.Thanks for all your help!Final Statement was:CREATE PROCEDURE [ins_3pm6] (@var1 varchar(30))ASINSERT INTO test VALUES (CONVERT(datetime,@var1))GO |
|
|
|
|
|
|
|
|
Converting with Inserts?