((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @mID
and convert(varchar,njdate,121)
between convert(varchar,@startdate,121)
and convert(varchar,@enddate,121)
Something weird happened tonight.. here's the deal. I have two databases exactly the same.. one is for dev, one's for live data.
Anyway, the live DB had an outdated view.. so I exported from the dev DB to the live.. just copying that one view over (using "copy objects" in the export DTS wizard). Very oddly, it actually copied over the data in the tables referenced by the view! Not good, cuz I told my coworkers I'd leave the data in the tables alone :( How do I copy a view over, but just the view definition, and NOT the actual table data??
I created sql job using maintenance plan to run DBCC Checkdb for all databases. But I didn't choose the option Ignore databases where the state is not online. If any db is restoring the same time as DBCC Check db job running, is check db job fails?
I have a select statement like this: select IsVerified from AppForm the IsVerified returns 'True' ...can I convert that value to 'Yes' by using some sort of function?
ok i am very very new at this, ive downloaded the SQL 2005 trial and i can not seem to figure this out. I have a .xls i want to make a active sql page out of. please please help me with this
the .xls is here http://www.mediafire.com/?5jtgzcxb232
Once a week there is a sqlscript, which should run and store data to a txt file.
There is a tablle in which imagedata is stored in RAW format(that one with small boxes, Qmarks and dots...).
Now i want to export it to the txtfile as HEX, so i can decode it by php further.
With the thumbnail, also stores there everything is fine, the SQL looks that way:
SELECt hex(thumbnail) FROM picTable where id_pic=54985
The Data is then displayed as HEX. But the standart picture is to big. When i use the statement: SELECt hex(bigpic) FROM picTable where id_pic=54985
i got the error message:
An error occurred while running the query.
The data type, length, or value of argument 1 of HEX is invalid.
(SQL code = -171, SQL state = 42815)
After some research i found that the maximum lenght is 16 336 bytes for the HEX-operation. The picture is nearly 30 000 bytes... :shocked: Have anyone an idea how to fix that? I am not able here to accsess to the database direct...
Hi All, I am in the process of changing the cursor based Proc with temp tables, I have one of the Procs which is beening used to get a value for the cursor, I am thinking if it is possible to change it to a function so that I can update the column at once.
[code] CREATE PROCEDURE[dbo].[get_cre_tijd_id] ( @tijd_datum datetime, @tijd_id int output ) AS
SET @datum_tekst = CAST(@tijd_datum AS VARCHAR); SET @datum_tijd = CONVERT(DATETIME, SUBTRING(@datum_tekst, 1, 12), 21 ) SET @tijd_id = (SELECT tijd_id FROM dim_tijd WHERE tijd_datum = @datum_tijd )
IF @tijd_id IS NULL BEGIN INSERT INTO dim_tijd ( tijd_datum , jaar , kwartaal , maand , dag , periode , week , weekdag ) VALUES ( @datum_tijd , datepart(yy,@tijd_datum) , datepart(q,@tijd_datum) , datepart(m,@tijd_datum) , datepart(d,@tijd_datum) , ceiling(datepart(wk,@tijd_datum)/4.00) ,dbo.get_iso_week(@tijd_datum) , datepart(dw,@tijd_datum) )
SET @tijd_id = (SELECT tijd_id FROM dim_tijd WHERE tijd_datum = @datum_tijd) END
I am subtracting two datetimes (last(fields!FaultStart.value))-(first(fields!FaultStart.value)) this works fine and returns a value in a textbox 5.23:44:23. I need to convert that number to minutes I am very new to sql programming and do not know very many commands. I am using Microsoft sql server 2005 express edition along with sql server business intelligence development studio where I placed a textbox and entered the expression above. I have also tried to use datediff command =datediff(second, (last(fields!FaultStart.value)),(first(fields!FaultStart.value))) but the second is underlined and tooltips says WRONG NUMBER OF ARGUMENTS I€™ve tried =datediff(€œsecond€?, (last(fields!FaultStart.value)),(first(fields!FaultStart.value))) and =datediff(€œ€™second€™€?, (last(fields!FaultStart.value)),(first(fields!FaultStart.value))) also for second I€™ve tried seconds. Any help would be most appreciated
I haven't seen a really good solution for this in amongst the threads about UTC. What I want to do is convert a date time to UTC, taking into account whether or not it is in DST, and be able to deploy this code to any server in the world without customizing the SQL code, and have it know how to convert, as long as a I feed it the local Time Zone.
I would think this would be easy because the OS knows at any given time what time zone has what UTC offset, and exactly at what time DST starts and ends. Right now I do this, but it is limited. I have a table that, for each site (there can be more than one site in different time zones) stores the UTC offset and whether or not the site observes DST. New York would have the "Observes_DST" set to 1, Phoenix would have it set to zero. The "Hours_To_UTC" value would be 5 for New York.
This code below assumes all sites are located in Canada or the US, as Canada and the US now use the second Sunday in March and first Sunday in November as the switch over times. (although this would work fine for countries like India and China that don't observe DST).
UPDATE a
SET Time_Arrived_UTC =
CASE
WHEN b.Observes_DST = 1 THEN
CASE
WHEN Month(Time_Arrived) IN (4,5,6,7,8,9,10) THEN
DATEADD(hh,b.Hours_From_UTC - 1,Time_Arrived)
WHEN Month(Time_Arrived) = 3 AND Day(Time_Arrived)- DATEPART(dw,Time_Arrived) >= 7 AND
Day(Time_Arrived) <= 14 AND DATEPART(dw,Time_Arrived) <> 1
then DATEADD(hh,b.Hours_From_UTC - 1,Time_Arrived)
WHEN Month(Time_Arrived) = 3 AND Day(Time_Arrived) > 14
then DATEADD(hh,b.Hours_From_UTC - 1,Time_Arrived)
WHEN Month(Time_Arrived) = 3 AND Day(Time_Arrived) >= 8 AND Day(Time_Arrived) <= 14 AND
DATEPART(dw,Time_Arrived) = 1 AND DATEPART(hh,Time_Arrived) >= 2
THEN DATEADD(hh,b.Hours_From_UTC - 1,Time_Arrived)
WHEN Month(Time_Arrived) = 11 AND Day(Time_Arrived) <= 7 AND DATEPART(dw,Time_Arrived) = 1 AND
DATEPART(hh,Time_Arrived) < 2 then DATEADD(hh,b.Hours_From_UTC - 1,Time_Arrived)
WHEN Month(Time_Arrived) = 11 AND Day(Time_Arrived)- DATEPART(dw,Time_Arrived) < 0 then
DATEADD(hh,b.Hours_From_UTC - 1,Time_Arrived)
ELSE DATEADD(hh,b.Hours_From_UTC,Time_Arrived)
END
ELSE DATEADD(hh,b.Hours_From_UTC,Time_Arrived)
END FROM
Facts_Table a INNER JOIN Site_Info_Lookup_Table b
ON a.Site_Name= b.Site_Name
This works, (and is much more efficient than using a UTC lookup table) and I could probably go about hard coding various other start and end DST datetimes for other time zone DST (Australia and Europe, for example), but that wouldn't be dynamic in case one country makes a change in policy that changes when DST starts and stops, like the US just did.
SQL Server only provides current time UTC conversion--I would think it should have a function like this:
ConvertToUTC(datetimevalue,timezone)
Oracle has a function like that, but it is useless because it doesn't take into account DST, it would for instance just add 5 hours to EST for every date you entered (now that's helpuful!). It is baffling that this function doesn't exist.
I am using Integration Services so I could probably build a script task that would query the OS for DST start and stop times given a particular time zone (if available), but I'm not much of a coder--would be nice to have an easy way to do it in Transact-SQL.
I'm needing to take a value inputted by a user via html form and convert it to a hex value upon inserting into SQL2000 db. I only need to store the 8 chars after "0x". Is there any T-SQL that can pull this off? CAST or CONVERT? Sorry if this is a silly question and hope I supplied enough info...
Hi I am converting data from old DB to NEW DBIn the OLD table fields like "PhoneNumber" the data enterd are [ 657 985-986, (03)-987-543, 675(89)00, ect]Is their any function in sql where I can get rid of all those spaces and () and - between the numbers as my new field is only numbers and with out spaceOtherwise I have to clean them up manually as I have 1000000 records cheers
I have a stored procedure which I user to retur an Email-address from aspnet_Membership (DB-table). Here is the code: ALTER PROCEDURE dbo.StoredProcedure2 @user nvarchar(256) AS DECLARE @id uniqueidentifier SET @id = '' SELECT @id = UserId from aspnet_Users WHERE UserName = @user SELECT Email From aspnet_Membership WHERE UserId = @id I use StoredProcedure2 in this way: Dim cmd As SqlCommand = New SqlCommand("StoredProcedure2", MyConnection) cmd.CommandType = CommandType.StoredProcedure 'send in UserName as @User to StoredProcedure2 cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value = User.Identity.Name.ToString() 'Return EmailAddresse returnValue = cmd.ExecuteScalar() Message I get is: "Conversion failed when converting from a character string to uniqueidentifier" Please help...
Hi, I am developing a project that using the one of the starter kits which use the MS SQL EXPRESS database.The project is almost ready to be launch. few questions:
I am looking for a good host with good support reasonable paid. What is my options if I would like to convert from the current database, to other databases like MySql, MS SQL Server or any? which tools can help with this convertions? thats all, thanks.
I have an old table (table1) and a new table (table2). I need to move some of the data from table1 to table2. For my example, table1 contains 1 field that is a DateTime, we’ll call it table1_Date. table2 also contains 1 field that is a SmallDateTime, we’ll call it table2_Date. I want to do something like this: Insert into table2 table2_Date Select table1_Date From table1 Where ….. I am getting the following error: The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. How can I go about converting this on the insert?
hi.. may i know how to convert from mysql to microsoft sql server ?? is it very troublesome ?? coz its quite a big project.. i need to convert frm mysql to sql.. is there any example or reference? thanks
Hello Everyone and thanks for your help in advance. I have an application that inserts a variety of values into a SQL Server database. Among the columns are three DateTime values. I have code working properly on my test server, but when I port the identical code to my production database, I get the following error:
Arithmetic overflow error converting expression to data type datetime. The statement has been terminated
When I remove any type of insert involving date, the application works. I have tried the date in various formats, for instance "09/12/2001" and "20010912", but still get the same error. Obviously, there must be differences in the SQL Servers, but I have never run into this problem before and the current server is running many applications involving dates. I haven't got a clue as to how to solve this problem. Any help is greatly appreciated. Thanks.
I have a control with checkboxes. The checkbox.checked property returns a boolean value. If I want to insert a record into a table that has a corresponding column of type bit, how do I do it?
I triedCType(myChkBox.checked,String), which returns the strings "True" or "False". But, I get the errorThe name 'True' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.I then tried "CAST(" & CType(myChkBox.checked,String) & ",bit)", but got the same error.
I can write a function that will return a 1 or a 0 but that seems ludicrous. Surely there must be a more elegant way to use the result of a checkbox in an SQL statement.
I have a tbale with maybe 30 columns and I'm selecting all where a record number matches a parameter I've passed. That works fine, however is there any way to do some conversions to fields when selecting without having to list out each column? For instance this is what it looks like now:
SELECT * FROM Losses WHERE @AppRecord = AppRecord ORDER BY Record ASC
But I want to convert say one column, is there a way to keep similar syntax instead of listing out each column?
SELECT *, CONVERT(varchar(15), [Losses]) as [Losses] FROM Losses WHERE @AppRecord = AppRecord ORDER BY Record ASC
And the column I'm converting is of type 'money'. I'm converting it to get rid of the extra zeroes at the end. If you know a better way to do it I'd be interested in knowing.
I recently got a new job at a new company. My previous experience had all been with Oracle DB's but the new place uses Sql Server 2k. I have a few general questions about how to do stuff in SqlServer...
1) how do I create a sequence and a trigger? 2) I know SqlServer probably doesnt have anything like Packages, so are procedures local to schemas? and if so, are schema's nestable in any way? 3) Can anyone reccomend a good book on T-SQL?
I have been given a Product table whoes all column types are varchar(8000) One of the column is Price and other is DecimalPosition. Price column includes price without any decimal place and the data in DecimlaPosition column determins where the decimal should be placed. So for instance, if the Price column includes '1000' and DecimalPosision includes '2' >> then it means that the actual price for this product is '10.00' and NOT '1000'. Similarly, if the DecimalPosision includes '3' >> then it means that the actual price for this product is '1.000' and NOT '1000'My question is that when I am getting the price for a product from this table, how can I get the price in the correct format, e..g like '10.00' and not '1000'Should I use SQL statements to convert 1000 into 10.00 or should I use some sort of programming logic to convert 1000 into 10.00.kind regards
I want to upgrade my 6.5 SQL Server to version 7, but it looks like I don't have enough disk space. I installed version 7.0 and launched the SQL Server Upgrade Wizard in hopes of converting my databases (objects and data) from 6.5 to 7.0. Instead, the wizard wants to make *copies* of my databases in the 7.0 instance. These databases are large and I don't have enough room for a second copy of each database. Is there no way to *convert* the existing .dat files from 6.5 to 7.0 format? I want to avoid doubling the disk capacity of my server.
Two datatype problems: (1) Datetime conversion (2) Foreign Currency format.
The first problem is that I am trying to convert source data from varchar to datetime. The source data is in CSV format and is displayed as follows - '1111999052349' to represent 1-Nov-1999 05:23:49. Have converted to a numeric value and then tried to convert to datetime but this just returns the following message 'Server: Msg 8115, Level 16, State 2, Line 3 Arithmetic overflow error converting expression to data type datetime.' However you can convert to a timestamp but the value returned is not meaningful (e.g. 0x0E0000013DFA4EE8).
Problem 2 concerns the French currency. The source data is in CSV format and is displayed as follows - '00000001,9700' to represent Fr1.97. Need to convert this to a numeric field or alternatively get SQL 7 to recognise it as a money field.
I need to convert a text field (which contains only numbers) into an int field. What would be the easiest way to do so. SQL 6.5. There are about 100, 000 rows in the db.
Currently writing a script to duplicate information in a database for a client. They have setup a userdefined table in our software that contains information that needs to go into the duplicate records. One particular column in this user defined table contains a decimal number in the 45mill range. The column that number is going into is an nVarchar column that requires a Hex value for our software.
Long story short (too late) I have been unable to find a system stored procedure that will convert the Decimal value into a Hexadecimal string.
Questions: Is there one I just haven't found?
Anyone know of one already written by someone in the Community?
Or stop wasting time looking because there isn't one so I better hurry up and get one written and tested before my project is due?
I am porting some application from AIX/C/Oracle to Windows/C#2005/MS SQL 2000. I have one query which has been giving me problems in translation from Oracle to MS SQL. This query had some decode statements which I changed to CASE statements. It also has Oracle (+) join operator, from what I have found this translates into an *= or =* depending which side the (+) is on.
Here is the query before replacing (+)
SELECT CSUM_TERR_CODE, INVM_COST_FLAG, SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)), SUM(CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = '85' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND CITM_COMP_CODE = INVM_COMP_CODE(+) AND CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND INVM_COST_FLAG = 'C' GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG UNION SELECT CSUM_TERR_CODE, (CASE INVM_COST_FLAG WHEN NULL THEN 'A'ELSE INVM_COST_FLAG END), SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)), SUM(CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = '85' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND CITM_COMP_CODE = INVM_COMP_CODE(+) AND CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND (INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL) GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG
and after replacing with *, I think I have them in the right place
SELECT CSUM_TERR_CODE, INVM_COST_FLAG, SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)), SUM(CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = '85' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND CITM_COMP_CODE =* INVM_COMP_CODE AND CITM_ITEM_CODE =* INVM_ITEM_CODE AND INVM_COST_FLAG = 'C' GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG UNION SELECT CSUM_TERR_CODE, (CASE INVM_COST_FLAG WHEN NULL THEN 'A'ELSE INVM_COST_FLAG END), SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)), SUM(CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = '85' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND CITM_COMP_CODE =* INVM_COMP_CODE AND CITM_ITEM_CODE =* INVM_ITEM_CODE AND (INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL) GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG
The Oracle run fine, but when run on MS SQL I get the following error. The table 'ACC_CUST_ITEMS' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.
Converting Oracle Queries to run on MS SQL 2000. This is query with a sub-query. If I run each query separatly I get results and if I use NOT IN sub-query I get no results. On oracle I get the correct result, but when moved to MS SQL I get nothing. Here is the original Oracle query and my converted query. Am I missing something?
Oracle SELECT INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE, INVM_DESC, INVM_UNIT_TYPE, INVM_LOC, nvl(INVM_REORDER_POINT,0), nvl(INVM_REORDER_QTY,0), nvl(INVM_COST,0), nvl(INVM_SUG_PRICE,0), INVM_DEV_CLS FROM ACC_INV_MSTR WHERE INVM_COMP_CODE||INVM_ITEM_CODE NOT IN (SELECT CITM_COMP_CODE||CITM_ITEM_CODE FROM ACC_CUST_ITEMS WHERE CITM_PROPRD BETWEEN '200706' AND '200710') AND (INVM_DEPT_CODE IN (SELECT DEPT_CODE FROM ACCSR03_DEPT) OR :ws_dept_flag = 1) ORDER BY INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE
MS SQL
SELECT INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE, INVM_DESC, INVM_UNIT_TYPE, INVM_LOC, ISNULL(INVM_REORDER_POINT,0), ISNULL(INVM_REORDER_QTY,0), ISNULL(INVM_COST,0), ISNULL(INVM_SUG_PRICE,0), INVM_DEV_CLS FROM ACC_INV_MSTR WHERE INVM_COMP_CODE + INVM_ITEM_CODE NOT IN (SELECT CITM_COMP_CODE + CITM_ITEM_CODE FROM ACC_CUST_ITEMS WHERE CITM_PROPRD BETWEEN '200706' AND '200710') AND (INVM_DEPT_CODE IN (SELECT DEPT_CODE FROM ACCSR03_DEPT) OR @ws_dept_flag = 1) ORDER BY INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE
hey guys, i need help in converting int to date. i've googled all over and most of all recommend using CAST or CONVERT. however, when i tried it to my SQL command, it didn't work. was i using it wrong? please help me figure it out.
here is the command i used:
Code:
select convert(datetime, starttime, 120) as starttime from table
one of the example of starttime contained in the db is 1170349200.
i'm almost desperate.. help please.. thanks in advance..
I'm trying to convert a nvarchar datatype to int (ie:1234-56-78 to 12345678) . These values are primary keys in two tables. Both these tables have 3500 rows of this key type. I want to convert this to a int so I can make it a AutoNumber primary key so I can increment it. Is this possible? If so, how do I do it. Do I need to delete the dashes first some how? I fairly new to database adminstration, so any guidance will be greatly appreciated.
So where I work is thinking about one day moving to SQL server. Right now they have indexed files that aren't normalized with repeating fields in them and lots of repeat data and blank space (so a customer number in one file may be stored literally in 10 other files that are easily realted). In the intrest of saving time and money I think that they will not normalize, index, or anything to any of these files. From what I hear it will be a straight field by field creation for the most part and preserving the primary keys.
My question: I keep thinking this is going to be massive hit on performance and maintaince. How much would converting in such a manner hurt the performance of their database and how much could it potentially add to maintaince?
