November 3, 2004 at 6:33 pm
Hey guys and gals,
First off this is a really awesome site, and I've enjoyed poking around a bit. It's been quite helpful.
I'm a newbie who has been working with SQL Server 2000 for about two days now, so any help you can give me in finishing this project would be greatly appreciated.
Like the thread title says, I have these tables and I want to first combine them and then get rid of the data that the user does not need to see. Next, I have to perform some changes to the table and have those changes fill the columns of the table that they have to replace (if that makes sense). For instance, suppose the date is given as November 3, 2004, I need to change it to 2004/11/03. Not exactly, but you get the point.
I've been able to get the table to look how I want it to look, the next question is how to perform the manipulations? How can I get the column read in, perform operations row by row and then output the resultant to the original table? In actuality, I need to conver from UTC time to the following format: yyyy mm dd hh:mm:ss.
Anyhelp would be appreciated. Thanks in advance!
-Peace-
November 3, 2004 at 8:44 pm
I'm sure that we can help you do, but you'll have to provide a bit more info before anyone can give you anything specific.
The general form of query that you'll probably end up using will look something like
insert into {tablename} (fieldname, fieldname, ..., fieldname)
select ...
from tablea inner join tableb ...
where ...
This will add new rows to {tablename} from any number of base tables. If you need to manipulate the data in any way, it is likely that this will occur as part of the select clause.
Once the data is in place and you decide that you want to modify it in some way, you write an UPDATE query.
update {tablename}
set field1 = value, field2 = anothervalue, ...
where
Note that, in general, data manipulation in SQL Server is best performed on a set of data, not on a row-by-row basis. The set of data affected is determined by a WHERE clause.
Anyway, enough of that, the best way to learn is to actually do it. If you provide a bit more information about tables, fields and what you would like to do, I'll assist more.
Regards
Phil
November 4, 2004 at 7:39 am
Good morning, Phil. Thanks for your offer to help me out. I think I have figured out how to combine the two tables, I've done a JOIN statement (see below)
Select *
from dbo.Message1_2_3 a join dbo.Message5
on a.Source = b.Source
This combines my two tables where the sources are equal, so the data in the combined table is all the data that I need.
Now, what I need to do is take the data found in the Time columns and convert that from it's present UTC format to the following format: yyyymmddhhmm. Please find the example data below, I will cut and paste it from excel:
| UTCTime |
| 38278.77 |
| 38278.77 |
| 38278.77 |
| 38278.78 |
| 38278.78 |
| 38278.78 |
| 38278.78 |
| 38278.78 |
| 38278.78 |
| 38278.79 |
| 38278.79 |
| 38278.79 |
| 38278.79 |
| 38278.79 |
| 38278.79 |
| 38278.79 |
| 38278.79 |
| 38278.8 |
| 38278.8 |
| 38278.8 |
| 38278.8 |
And I need to convert those time values to the format given above.
I know how to "hardcode" a date into my code to output the correct date/time, but I don't know how to perform this operation on the entire row. I will include my code for decoding a "hardcoded" value:
SET DATEFORMAT mdy
go
DECLARE @datevar smalldatetime
SET @datevar = 38279.813344907401
SELECT @datevar
go
so my input is 38279.813344907401 and my output on the screen is 2004-10-21 19:31:00
I look forward to hearing from you and your invaluable feedback!
Regards,
DH
November 4, 2004 at 5:09 pm
It's easier than you think - you're already on the right track. Try this:
declare @utcdate as float, @cvdate as datetime
set dateformat mdy
set @utcdate = 38279.813344907401
set @cvdate = @utcdate
select @utcdate as UTCDate, @cvdate as SQLDate
Regards
Phil
November 5, 2004 at 8:16 am
Phil,
Thanks for the tip, however, I notice that you're still hardcoding the data into the "set @utcdate =" command. What I want to do is automate this process so that the query will automatically join the two tables, grab the data I want to manipulate, perform the conversion, and then replace the data in the original table with the re-formated data. I'm not sure if I've made that clear.
What I'm thinking of doing is having the query go down row by row and extract the UTC time data from the table, convert it to the windows format, and then replace the UTC time with windows time.
I don't see why I can't replace the hardcoded value (38279.813344907401) with a command that says something like TableA.UTCtime or something like that. Am I missing a for loop or some convention similar to that?
I'm quite confidant you will provide me with the answer and tips that I'm looking for. With that in mind, I'm going to want to export my new table into a text file with column headings, is this something that can be done with the SQL Query function? I know I can do it from the SQL Enterprise Manager. Do I need to pass my new table to the Manager to perform this function? If so, how?
Best Regards,
DH
November 5, 2004 at 6:49 pm
OK, assume that table 'tbl' contains two fields, 'utc' and 'date'. utc is populated, but date is empty. Here is a query that will populate date from utc:
update tbl
set date = cast(utc as datetime)
go
Is that more like what you were looking for?
It's not straightforward to get query headings out of QA, but, as you say, EM does this for you. To run a select query in EM, just right click on any table in your database and choose Query - you'll get a SQL pane where you can just paste your query and run it.
Alternatively, create a new view in EM that is based on your SELECT statement and use that much as you would a physical table.
Regards
Phil
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy