Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Time Conversion Formula

Does any of you have an SQL example that will convert a given date to the number of seconds since 1970? We have one that does the opposite (seconds to date), but can't figure out how to go date to seconds.

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Current Date/time Formula
Hi,I would like to have a date_last_modified field for one of my SQLtables. Instead of coding my front end to keep up with this field isthere a way to user a formula for this column in Enterprise Manager sothat each time a record is created or updated the current date/timewill be inserted/updated? I tried using getdate() but then the fieldalways has the current time, which is not what I am looking for. I amlooking for the time the row is updated or created. Is this possible?Thank you!

View Replies !   View Related
Conversion Of Oracle Date Time To Sql Server Date Time In SSIS
This is driving me nuts..


I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.


I'm struggling with this for a quite a while and I'm not able to get it working.


I tried the oracle query something like this,



this gives me an output of 20070511  23:06:30:000


the space in MM : SS is intentional here, since without that space it appread as smiley  


I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data


I'm struck with error for hours now.   Any pointers would be helpful.



View Replies !   View Related
Conversion For Time
I can get my DB to accept my date by doing the following:  row.Item("RequestDate") = Me.fullDate.Date -----I have fulldate dimensioned as date above.  However if I try to do the follwing for a Time it gives me an error when it trys to update the DB the column is set to datetime & when I check the value of the row Item in my command window it says
?row.Item("BeginTime")#6:00:00 AM# {Date}[Date]: #6:00:00 AM#
row.Item("BeginTime") = CDate(ddlBegin.SelectedValue & beginAMPM)row.Item("EndTime") = CDate(ddlEnd.SelectedValue & endAMPM)The SQL Error I get is the following:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.Source Error:

Line 335: row.Item("EndTime") = CDate(ddlEnd.SelectedValue & endAMPM)
Line 336: DsVacationData1.RequestData.AddRequestDataRow(row)
Line 337: SqlDataAdapter2.Update(DsVacationData1)
Line 338: DsVacationData1.AcceptChanges()
Line 339: End SubThanks for any help.

View Replies !   View Related
Time Conversion

I'm don't seem to be able to find any examples for creating a timesheet app.

For example I have the following columns in mind

Date datetime
StartTime datetime
FinsishTime datetime
Lunch int

I want the last column to be computed and do a datediff between startTime
& FinishTime - Lunch.

Can someone advise me on the correct syntax.

Thanks in advance.

View Replies !   View Related
Time Conversion
I'm using SQL server and in the database the 'epDischargeTime' is stored as '51600' for example. How I make sense of this and convert it to something like 10:00am or something. I need to find the % of patients that were discharged before 10am. Thanks in advance!

View Replies !   View Related
IBM Time Conversion
I will try keeping this short. I am connecting to DBS on an AS400. One of the columns in the table in DB2 that I am trying to get is described as ISO Time. When I use AS400 operations navigator and run a SQL query on the column I can see the time in the correct format Eg: 12:27:26. So the source looks ok.
I then connect to DB2 in SSIS, using the ODBC driver etc etc. I have a data reader source that then connects to the DB2 table, using a SQL command and gets the data. Ideally this would then go straight into a OLE DB Destination to get to my SQL Server. I have put a data viewer between the Data Reader and the destination to see the actual values coming through, and the time column is now being displayed as 44846000000.
I have tried using a Data Conversion task, but no combinations seem to get the time back into the correct format.
Any ideas?

View Replies !   View Related
Conversion: Time To Date
I'm setting up a website for a new employer and their existing database.
The table I'm using has a Time field in it that captures the date and time of the record.
I'm trying to tie that field into some label controls and can't seem to figure out how to convert the time to just a date. I tried Format(TimeColumn, "MM/DD/YYYY") which does nothing but put my intended formatting as the label. What do I need to do to convert the time to just a date?

View Replies !   View Related
Time Conversion Hiccup
Hi,ddl & dmlproject varchar(10) start char(5) stop char(5)------------------------- ----- -----hey now 21:00 19:25new test 20:25 20:30t 10 21:00 NULLt 11 21:10 21:35t 12 21:30 22:40t 12 7:05 11:10test me 08:00 14:25test me 17:00 17:55what I want is to calculate time duration using hour (h.1decimal) e.g.1.2 :what I have now using the following query:select project, start, stop,CASE WHEN (datediff(n,start,stop) < 0) THEN -1WHEN (datediff(n,start,stop) < 1) THEN (CAST(datediff(n,start,stop)as decimal(1)))ELSE Convert(decimal(1),(datediff(n,start,stop)/60)) END astotal_hoursfrom testTBlgroup by project, start, stopoutput:project start stop total_hours------------------------- ----- ----- -----------hey now 21:00 19:25 -1new test 20:25 20:30 0t 10 21:00 NULL NULLt 11 21:10 21:35 0t 12 21:30 22:40 1t 12 7:05 11:10 4test me 08:00 14:25 6test me 17:00 17:55 0If the calcuate is right I'd like to remove start and stop columns,so, it would just return project and the sum of hours including lessthan an hour in decimal for each.Thank you.

View Replies !   View Related
Date Time Conversion

I just had problem with Date Time conversion. Here is the example:

Select GetDate()

Result: 2000-08-02 23:50:15.280

Then I use Convert function:

Select Convert(DateTime,Cast(GetDate() as varchar), 101)

Result: 2000-08-02 23:50:00.000

What I expected to see is: 08/02/2000 and it was what I used to get.

Can anybody helpe to solve this problem? I am running sql server 7.0



View Replies !   View Related
Time Conversion Issue
Im storing in a table the value corresponding to average answer time of incoming calls of our call center. The time is kept in seconds, however I need to show this value in minutes not in seconds, if I divide the seconds by 60 , I don€™t get the correct time in minutes, for example:
Time in minutes : 00:02:16 (2 minutes and 16 seconds)
Time in seconds : 136 Seconds (that€™s the value stored on my table)
Time in seconds converted in minutes : 136 / 60 = 2,2666666 minutes
I want to get   2,16 minutes
Is there any way to do this conversion (any sql function)?, and also is it correct to do this conversion ?
Thanks for your advices.

View Replies !   View Related
Julian Date Time Conversion
Can anyone tell me how to convert julian date time to DateTime and Vice Versa?the function which I have only convers the date to Julian and julian to date but the time is not appended. How can i get the time into Julian format and from julian format?Any help would be appreciated.thanks.

View Replies !   View Related
Date An Time Conversion Problem
Well here is the problem iam trying to evaluate a expresion and return a string but when i run my code it always return where my expresion is false where it should return true. here is my code.

DECLARE @datefin_flag char(13), @strip datetime
select @strip = getdate()
--select convert(char(10),@strip,120)
select @datefin_flag = dateend FROM mattstest WHERE convert(char(10),datebegin,120) <= convert(char(10),@strip,120) and convert(char(10),dateend,120) >= convert(char(10),@strip,120)
--select @datefin_flag
--UPDATE dateflagevent SET flagevent = getdate() FROM dateflagevent
IF (@datefin_flag = @strip)
print 'Run'
print 'You cant run this'

Now here is the my table data:

datebegin datefin
------------------------ ------------------------
2004-12-25 00:00:00.0002005-01-25 00:00:00.000
2004-11-25 00:00:00.0002004-12-24 00:00:00.000
2005-02-25 00:00:00.0002005-03-25 00:00:00.000

I think that the problem is the date and time they are the same but not in the right format its like saying 2004-01-25 is equal to janv 25 2005 how do i correct this.

View Replies !   View Related
Date And Time Together Conversion To Yyyy:mm:dd Hh:mm:ss
I have 2 fields with data like
20040201 and 122235

Combined they need to make
2002-02-01 12:22:35

I can convert them separately to dates just fine. But when I try to combine them and convert missing something. here is what I have so far

select convert(datetime,(convert(datetime,[Dateproduced],112)+'
'+(left(timeproduced,2) + ':'+ substring(timeproduced,3,2)+':'+right(timeproduced ,2),120)))
from Demographic_staging

View Replies !   View Related
DTS Package Date/Time Conversion
I''m working on a data conversion process and trying to convert data from SQL Server to Oracle 10g. The problem I'm encounterin g is that when I go from SQL to Oracle with a date into a Timestamp field in Oracle, it errors out. Not because it's only Timestamp, Oracle accepts dates in that field type. I don't know why this is happening, but I'm sure someone else has run into this...thoughts?

View Replies !   View Related
Date/Time Conversion Problem
After upgrading to SQL 7 we found that all our datetime fields now contain milliseconds. Our applications due extensive datetime validation and those routines are now failing because the date/times don't match up with milliseconds. Does anyone know if there is an easy way to turn off the milliseconds on a datetime field, or another easy solution to this problem?


Jim Franz

View Replies !   View Related
UNIX Time Conversion Functions
A common problem in moving data between SQL Server and UNIX systems is converting to/from the SQL Server datetime format to the UNIX time format.

There are several UNIX time formats, but the most common is a signed 32-bit integer that represents time as the number of seconds between 1970-01-01 00:00:00 and a given time. The functions in the script can be use to convert between SQL Server datetime and this UNIX time format.

For more information on UNIX Time, please read this link:

For more information about SQL Server date/time conversions, refer to this link:
Date/Time Info and Script Links

The conversion of UNIX Time to SQL Server datetime is fairly trivial using the SQL Server DATEADD function, and this is the logic used by the F_UNIX_TIME_TO_DATETIME function in the script:

declare @UNIX_TIME int
select @UNIX_TIME = 1111111111
-- Using dateadd to add seconds to 1970-01-01
select [Datetime from UNIX Time] = dateadd(ss,@UNIX_TIME,'1970-01-01')


Datetime from UNIX Time
2005-03-18 01:58:31.000

(1 row(s) affected)

The conversion of SQL Server datetime to UNIX Time is more complex. SQL Server datetime is accurate to milliseconds so is necessary to either truncate or round off the time to a whole second. The function in the F_DATETIME_TO_UNIX_TIME script rounds the time down if milliseconds is less than 500 and up otherwise. A second problem is that UNIX Time is an integer, so it can only represent time from 1901-12-13 20:45:52 through 2038-01-19 03:14:07. The range of SQL Server datetime is 1753-01-01 through 9999-12-31, so the function in the script has logic to return a NULL if the datetime is outside the valid UNIX Time range. Another minor issue is that the SQL Server DATEDIFF function will not cover the full range of an integer value with seconds, so it is necessary to have additional logic in the function do cover the time from 1901-12-13 20:45:52 to 1901-12-14 00:00:00.

The function names created by this script are:

The script also includes code to test and demo the functions.

if objectproperty(object_id('dbo.F_DATETIME_TO_UNIX_TIME'),'IsScalarFunction') = 1
begin drop function dbo.F_DATETIME_TO_UNIX_TIME end
create function dbo.F_DATETIME_TO_UNIX_TIME
( @DAY datetime )
returns int

Finds UNIX time as the difference in seconds between
1970-01-01 00:00:00 and input parameter @DAY after
rounding @DAY to the neareast whoie second.

Valid datetime range is 1901-12-13 20:45:51.500 through
2038-01-19 03:14:07.497. This range is limited to the smallest
through the largest possible integer.

Datetimes outside this range will return null.
declare @wkdt datetime

-- Return null if outside of valid UNIX Time range
if @DAY < '1901-12-13 20:45:51.500' or @DAY > '2038-01-19 03:14:07.497'
return null

-- Round off datetime to nearest whole second
select @wkdt = dateadd(ms,round(datepart(ms,@DAY),-3)-datepart(ms,@DAY),@DAY)

-- If date GE 1901-12-14
if @wkdt >= 712return datediff(ss,25567,@wkdt)

-- Handles time GE '1901-12-13 20:45:52.000 and LT 1901-12-14
return -2147472000-datediff(ss,@wkdt,712)

if objectproperty(object_id('dbo.F_UNIX_TIME_TO_DATETIME'),'IsScalarFunction') = 1
begin drop function dbo.F_UNIX_TIME_TO_DATETIME end
create function dbo.F_UNIX_TIME_TO_DATETIME
( @UNIX_TIME int )
returns datetime

Converts UNIX time represented as the difference
in seconds between 1970-01-01 00:00:00 to a datetime.

Any valid integer -2,147,483,648 through 2,147,483,647
can be converted to datetime.

return dateadd(ss,@UNIX_TIME,25567)


Demo functions F_DATETIME_TO_UNIX_TIME and
F_UNIX_TIME_TO_DATETIME by converting a datetime
to UNIX time and back to datetime.

[Input Datetime] = convert(varchar(23),DT,121),
[UNIX Time] = dbo. F_DATETIME_TO_UNIX_TIME(a.dt),
[Datetime from UNIX Time] =
-- Convert datetime to UNIX time an back to Datetime
Note = .a.note
selectDT = getdate(),
Note = 'Current date'
union all
selectDT = dateadd(ms,500,getdate()),
Note = 'Current date + 500 ms'
union all
selectDT = dateadd(ms,750,getdate()),
Note = 'Current date + 750 ms'
union all
selectDT = '1901-12-13 20:45:51.500',
Note = 'Earliest datetime function can convert'
union all
selectDT = '2038-01-19 03:14:07.497',
Note = 'Last datetime function can convert'
union all
selectDT = '2001-09-09 01:46:40',
Note ='UNIX time 1000000000'
union all
selectDT = '2005-03-18 01:58:31',
Note = 'UNIX time 1111111111'
union all
selectDT = '2009-02-13 23:31:30',
Note ='UNIX time 1234567890'
union all
selectDT = '1901-12-14 00:00:00.000',
Note = 'Date time dateadd second limit'
union all
selectDT = '1901-12-13 23:59:59.000',
Note = 'Date time dateadd outside second limit'
union all
selectDT = '1901-12-13 20:45:51.497',
Note = 'Date time function cannot convert - low end'
union all
DT = '2038-01-19 03:14:07.500',
Note = 'Date time function cannot convert - high end'
union all
DT = '1753-01-01 00:00:00.000',
Note = 'Min Datetime'
union all
DT = '9999-12-31 23:59:59.997',
Note = 'Max Datetime'
) a


Input Datetime UNIX Time Datetime from UNIX Time Note
----------------------- ----------- ----------------------- --------------------------------------------
2006-05-29 23:34:11.517 1148945652 2006-05-29 23:34:12.000 Current date
2006-05-29 23:34:12.017 1148945652 2006-05-29 23:34:12.000 Current date + 500 ms
2006-05-29 23:34:12.267 1148945652 2006-05-29 23:34:12.000 Current date + 750 ms
1901-12-13 20:45:51.500 -2147483648 1901-12-13 20:45:52.000 Earliest datetime function can convert
2038-01-19 03:14:07.497 2147483647 2038-01-19 03:14:07.000 Last datetime function can convert
2001-09-09 01:46:40.000 1000000000 2001-09-09 01:46:40.000 UNIX time 1000000000
2005-03-18 01:58:31.000 1111111111 2005-03-18 01:58:31.000 UNIX time 1111111111
2009-02-13 23:31:30.000 1234567890 2009-02-13 23:31:30.000 UNIX time 1234567890
1901-12-14 00:00:00.000 -2147472000 1901-12-14 00:00:00.000 Date time dateadd second limit
1901-12-13 23:59:59.000 -2147472001 1901-12-13 23:59:59.000 Date time dateadd outside second limit
1901-12-13 20:45:51.497 NULL NULL Date time function cannot convert - low end
2038-01-19 03:14:07.500 NULL NULL Date time function cannot convert - high end
1753-01-01 00:00:00.000 NULL NULL Min Datetime
9999-12-31 23:59:59.997 NULL NULL Max Datetime

(14 row(s) affected)

Edit: Fixed minor bug that caused an overflow, instead of returning NULL, if input to function F_DATETIME_TO_UNIX_TIME was >= 9999-12-31 23:59:59.500.


View Replies !   View Related
Date Time Conversion Problem
Hi,I've run into a date conversion problem.When my package starts, I use a SQL Execute task to insert a record into a table. I set the SQLStatementSource value as follows:

"INSERT INTO tblUploadHeader (ExecutionGUID,
StartDateTime) VALUES ( '" +   @[System::ExecutionInstanceGUID] + "', '" + 
(DT_WSTR, 20) @[System::StartTime] + "' )"

which evaluates to

INSERT INTO tblUploadHeader (ExecutionGUID,
StartDateTime) VALUES ( '{C913A6EC-5DB9-405E-82DB-4F46DE454EEB}', '14/02/2006
11:53:32' )

StartDateTime is a column of type datetime. The INSERT results in the error:

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
SQL Server is treating this as the 2nd day of the 14th month. I don't necessarily have control over the language of the destination SQL Server. Ideally I would like to format this as '14 Feb 2006 ...' which is totally unambiguous. Can anyone suggest a way of doing that in the expression editor, or some other workround?
- Jerzy

View Replies !   View Related
Restoring During Daylight Saving Time Conversion
Here is an idle speculation question that came up, but I do not have an answer for.

At 2:00 AM on Oct. 31, we enter/leave (I never get it straight) Daylight Savings time. This means, we get to live through 2004-10-31 01:00:00.000 to 2004-10-31 02:00:00.000 twice, in somewhat rapid succession. The question is, what do you do if you need to restore a database to a point in time during that 2 hour window. One theory is to set a log mark at the second 2004-10-31 01:00:00.000, in order to differentiate the two hours. I am somehow not satisfied with that.

Like I said. This is an idle speculation, and could well be forgotten on Nov 1.

View Replies !   View Related
Datetime To Time Conversion With Default Date

I am importing a csv file to SQL 2005 table. The source column is coming as datetime. The destination filed is a datetime type. I would like to update the destination with the time part from the source. I used the data conversion to convert it to time using "database time[DT_DBTIME]". For a source value "2/08/2007 21:51:07" this inserts a value "2007-08-03 21:51:07.000". I need the column to have a value as "1900-01-01 21:57:07.000".

Can someone please tell me how do I do this conversion?


View Replies !   View Related
Data Conversion &&amp; Saving Takes Time
The following T-SQL code is run in a (2003) module.
I am acquiring data from an OPC server into an array of data type object. It is necessary to declare the array as an object for the OPC server to return data. The OPC returns the data in 15 mS.
I now need to save this data to a table in SQL 2005 running on a 2003 server.
The table for saving the data has already been created and saving the data is actually an 'update ... set ... where' statement.
The statement is
For i as short = 1 to itemCount

sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = " & itemValue(i) & " where TagName =' " & tagName(i)
The TagValue field datatype is decimal(18,6)
The itemValue(i) datatype is object / variant. The itemValue array contains 95%  values of type Single and the rest are Integers.
The TagName datatype is varchar(50)
The tagName(i) datatype is string
When I run the loop (255 iterations) with the above query it takes 1500 mS.
If I force a conversion by changing the query to
sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = Convert(decimal, " & itemValue(i) & ") where TagName =' " & tagName(i)
it takes 900 mS to execute for 255 iterations.
I tried one more variation
sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = Convert(decimal(18,6), " & itemValue(i) & ") where TagName =' " & tagName(i)
which takes 1200 mS to execute for 255 iterations.
If I use the following code
dim tempValue as decimal = 123456789012.123456D
sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = " & tempValue & " where TagName =' " & tagName(i)
it excutes in under 100 mS for 255 iterations.
There seems to be a problem during implicit / explicit conversion of type object/variant to decimal.
I need to save the data received from the OPC (255 tags) in under 150 mS.
Could somebody help?

View Replies !   View Related
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.

Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.

View Replies !   View Related
MDX Formula

Could you help me. I created a Report of the following form.

2005 2004
Sales 13000 12500
Variable Costs 9000 8750
Distribution Variable 1500 1423
Total Variable Costs 15756 14567
Variable Margin 12820 12000
% of Sales 591.69% 576%

But in order to obtain the colum for 2005 and 2004 I am using the following formula: Sum({Ytd()} ,[Versión].[Versión].&[1]). So when I try to obtain the row " % of sales" those % area acumulated too. When the result should be : 68.61 and 96%

What I need to do or what formula I need to use in order the % of Sales are not accumulated??? :


View Replies !   View Related
I am trying to build a database and I need to use the Formula field when creating a new coloumn!

I have built a table (tlbOrdersProducts)that contains te following fields:

orOrderId (int) FK
orProductId (varchar(50)) FK
orQuantity (int)
orPrice (float)

and another table (tlbProducts) that has the following fields:

prProductId (varchar(50))
prProductName (varchar(50))
prPrice (float)

Now I wanted to make a formula for the coloum tlbOrdersProducts.orPrice that automatically multiplies the tlbOrdersProducts.orQuantity * tlbProducts.prPrice

does anyone know how this can be done!!??

thanks for any sugestions and help!

View Replies !   View Related
SUM(IF( Formula
Having a hard time writing a formula in RS...
Trying to say if the funding date is equal to today or before the beginning of the month then sum the loan amount.
Looking for something like this SUM(IF(Funding Date,>=Date(),Loan Amount))
Know it not right but someone please help!

View Replies !   View Related
Formula In SQL

I am create a database and want to store some value automatically in some field i.e say i have 3 column 1) salary(int),2)tax(int) and 3rd field total salary(float) . i want to automatically fill total salary field as Column1-Column2 while i daont have any Idea How to do that?
please help me?
any idea or tutorial..example anything...

View Replies !   View Related
Formula In Sql Server
hi,my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application

View Replies !   View Related
Getting Values Into A Formula
hi there.
I have asked this question before in a different section of the forum without much reply, and its probably because i was asking the wrong people.
I need to calculate a qouta for an election.
SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1);
int quota =  (count(Vote) + 1) / ((11) + 1);
My problem is this: how to i get the count(vote) value from the Sql Statement to the formula.

View Replies !   View Related
Formula Not Working
This does nothing:([rush24] + [rush6] + [addLocationsTotal] + 50)
This gives me my total correctly:([rush24] + [rush6] + 50)
All column are numeric(9) except the "total" column which is numeric(13).
Why is this happening?

View Replies !   View Related
Using Formula For Colum Name!!
i wonder what is the best approach to use !!!i have creditLimit column in Customer Table the default value will be 500 and this limit to allow users to send sms from my website ..... every month they will be allowed to send 500 sms referring to the credit limit column.... now !!if the user sent today some sms and after few days sent another 20 and after one week he sent 150  SMS as a total so that means he has only 350 SMS as credit to use this month!!!so what is the best approach to implement this solution ? shall i have another field in the table called 'CreditUsed' and that will be updated each time the user will send SMS and this value will be compared with the credit limit ORi use the formula for the column to calculate the credit left and do the maths !!!what is the best approach as you think ???thnaks for reading this question !!

View Replies !   View Related
Formula Parsing
Hi,I have three tables in the following structure (simplified):Table 1: Containing the customers-------------------------------------------------create table Customers([cusID] int identity(1, 1) not null,[cusName] varchar(25) not null)Table 2: Containing the customer data fields---------------------------------------------------------------create table Data([datID] int identity(1, 1) not null,[datName] varchar(25) not null,[datFormula] varchar(1500))Table 3: Containing the customer data values-----------------------------------------------------------------create table Values([cusID] int not null,[datID] int not null,[valValue] sql_variant)In this structure the user can add as many data fields to a customer ashe wants (e.g. Country, City, Email, Phone, ...). I have added triggerswhich create a view similar to a pivot (I am working in SQL 2000) andadd triggers to the view so it is insertable, deletable and updateable.What I would like to do, is allow the user to create new fields wherethe values are based upon a calculation. This calculation would be donethrough a formula similar to what he would do e.g. in excel (thisformula is stored in the dimFormula field then).An example might help. Let's assume the user created a field 'Sales'(containing last year's sales) and 'Invoices' (containing the number ofinvoices that were created for him last year). Now, he wants to createa field 'AvgSales' with the formula '[Sales]/[Invoices]'.(Note that through adding these data fields, the above view was created(let's assume it is called vw_Customers and contains the columns [ID],[Name], [Sales], [Invoices], [AvgSales]).What I am looking for is a function which can parse this formula into at_sql query which runs the calculation. So, the formula'[Sales]/[Invoices]' would be translated into (let's assume there areno records with NULL or zero invoices):update vw_Customersset [AvgSales] = [Sales]/[Invoices]from vw_CustomersI am able to do the above with simple calculations (where you can evenuse sql functions e.g. year, len, ...). Now I would like to take thisone step forward into the possibility of using functions with morevariables.For example. Let's assume, the user wants to add a rating (field called'Rating') to his customers based upon the result of 'AvgSales. Heenters the formula 'if([AvgSales] > 2500, 'A', 'B')'.If anyone could help me on this, I would be very grateful. Thanks.M

View Replies !   View Related
Formula Problem
env: sql server 2000objective:add a formula for an INT column to the FORMULA field or the DEFAULTVALUE field in DESIGN VIEW.all of the following attempts failedIIF (columnName = 0, "1", columnName + 1)IIF (columnName = 0, 1, columnName + 1)IIF (0, "1", columnName + 1)caveat: cannot use IDENTITYREASON: I'd like have sets of repeatable values for this row, e.g.set a1 -- row 12 -- row 2345set b1 -- row 623set c1 -- row 9234Underlying rationale is to support OO design I understand probably it'sgoing to be huge headache for lots of people down the road but ...TIA.

View Replies !   View Related
Formula Column.
HI,I have a problem in formula column.I have 8 1 bit varibles in a tablefor ex: Flag1, falg2, flag3 ...Flag8Now I want to create another variable as a small integer and copy allthe flgas to that field.For ex:(flag1 << 0x80) | (flag2 << 0x40) | ..... | flag8I tried all possible ways?Let me know how to write the formula for this column.Thanks,Venkat.

View Replies !   View Related
Formula Column
Hi,I would like to create a calculated column using the formulasection for a table. I am having some trouble doing this.The table's name is ReportParameter. The calculated column's name istbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.I would like to use an If then statement such as the following (inpsuedo code):If tb1 = 1 then tbcalculatedcolumn = 1Elseif tb2 = 1 then tbcalculatedcolumn = 2EndifThanks for the help,Bill

View Replies !   View Related
Formula Columns
Can anyone tell me how to find out if a column is a 'formula' or computed column - and what the formula is?

sp_columns @table_name = 'table' gives me most of the stuff I want but it doesn't show what the formula is for any formula columns...

Cheers in advance,

View Replies !   View Related
Using The Formula Proprity
I want to use the proprity Formula related to a column propreties :
Exempel : table XFRS i want to pupulate the field XF2 (int 4) using the value of another Field XF1 (int 4) Which is an identity field.
But When i try to insert into the table XFRS then i had an error
"Insert faild because the following set options have incorrect settings"

Anyone to help me ? Thinks

View Replies !   View Related
SQL Formula Field
so this should be easy, but I cant seem to find any documentation on this.

I have 8 tables (one of which is a summary table).
I'm using the SQL Enterprise manager and went into the database to design my tables. I need one of the columns in the summary table to sum up the values from the corresponding rows in the other 7 tables. I'm trying to use the formula field but I cant seem to get it right. Keeps throwing back a formatting error.

Does anyone know the correct format or have an example of what should go in this field?

View Replies !   View Related
Cross Tab Formula
in front end application,iam using pivot table,there is no option to create the growth rate calcuation/formula in FE.

my table data consists like below:
country_name Revenue PERIOD_TYPE_OUT Amount_out
UK Solutions01 Apr 2007 To 31 Mar 200856.83000000
UK Solutions01 Apr 2006 To 31 Mar 2007116.07000000

while iam using the cross tab in front end application data view is coming as

01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008
solution solution
uk 116.07 56.83

Actual o/p should be:

01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 Growth
solution solution
uk 116.07 56.83 -0.51

can anybody tell how to calcualate growth in stored procedure level formala for calculating growth rate
is :
([current year]-[previous year])/[previous year]

View Replies !   View Related
Cast Formula
I have a quick question. created a report with this formula below. It works when I put a date range from 1-1-2007 to 2-1-2007, but when I put in a date range of 03-01-2008 through 03-31-2008. i get an error message of arithmetic overflow error converting numeric to data type numberic.

So instead of haveing the formula below be (4,2) I put it as (5,2) and now it work. Why is that?

CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings

View Replies !   View Related
SQL Formula Error
COUNT(CalibScoreAVGQA) AS Expr1
FROM dbo.TechPhonesCalibScoreAVGQA

Any idea why I'm getting "dbo.TechPhonesCalibScoreAVGQA.ID is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause

View Replies !   View Related
Maths Formula's In SQL

I want to store, mathematical formulas, in SQL, now how can i store integration signs, fractions, and various types of other symbols.

The output must be on html page using ASP. and also on VB frontend.

Pls. assist.

View Replies !   View Related
How To Put Formula In Sql Filed
i have two tables .Table 1 contains certain columns with values.Table 2 shud get those data from table1 into its column and display the calculated result. Any idea how do i go abt it,bcuz table2 shud contain a formula so as to calculate.

View Replies !   View Related
Sql Script Formula

hy guys
i am trying to calculate a formula in sql, but
in the field lbs1 sometimes has ceros and the lbs2 sometimes has ceros as well
the resul of the formula is null, but this is supposed to be a value. the rest of fields always have data.
SQRT((((db_linear_density_1.[Linear density_1] * dbo.Assay_acquire.[lbs1(Kg)] + PD_Data.dbo.db_linear_density.[Linear density_2] * dbo.Assay_acquire.[lbs2(Kg)]) / (dbo.Assay_acquire.[lbs1(Kg)] + dbo.Assay_acquire.[lbs2(Kg)])) * ((dbo.HOLELOCATION.DEPTH - PD_Data.dbo.drillhole_base_MOR_MET_2004.stem) * 0.3048)) / (((dbo.Assay_acquire.[lbs1(Kg)] + dbo.Assay_acquire.[lbs2(Kg)]) / dbo.Assay_acquire.holetons) * (dbo.Assay_acquire.holetons / (dbo.HOLELOCATION.DEPTH * 0.3048 * dbo.Assay_acquire.[holearea(m2)]))) / 15) * 0.9325

View Replies !   View Related
Need Help To Formula Sql Query For The Following
Table A                                                    Table B

101 Martha Steward Corp                       101 Martha Steward

102 Gloria Gardens                                101 John Pickle

103 Judi's Hockey Service                      101 Ray Lettuce

104 Randy Bowling Ball                          101 Mike Salad

105 Byron Ad Agency                            102 Gloria Williams

102 Bob Jeans

103 Judi McClair

103 John Nolte

104 Randy Koolaid

104 Ike Smith

104 Blade McRay

105 Byron Exec

105 Jim Rayburn

SQL result list

CustID        Customer                 Contact1               Contact2              Contact3

101     Martha Steward Corp         Martha Steward    John Pickle           Ray Lettuce

102     Gloria Gardens                  Gloria Williams     Bob Jeans            null

103     Judi's Hockey Service        Judi McClair          John Nolte           null

104     Randy Bowling Ball            Randy Koolaid      Ike Smith             Blade McRay

105     Byron Ad Agency              Byron Exec          Jim Rayburn         null

My knowledge level? create a scalar procedure to loop thru Table B using cursor three time to bring back one contact at a time. this just seem inefficient. can I make one pass thru the data. 

View Replies !   View Related
Reverse Formula

Hi . My database seems to be somehow encrypted by the software I used. When I try to export my data some fields are encrypted like this:

1 => 31911
2 => 63758
3 => 95540
4 => 127258
5 => 158912
6 => 190504
7 => 222032
8 => 253498
I'm really sure it's not some complicated formula (because I managed to recover other fields) . I just want to reverse the formula used to "encrypt" this data.
Thank you. Excuse my english.

View Replies !   View Related
Formula Question
I have this crystal formula

How can this be converted to a reporting services Expression


if {ITEMCODE} in ["A","B"]

then "TypeA"

else  if

 {ITEMCODE} in ["C","D"]

then "TypeB"





View Replies !   View Related
Placing A Formula

Hi everyone, I have a small dilema here. I am running SSRS 2000, and I have a report that is grouped by Part Number. I have   =Fields!Formula.Value < .1     so I can view only what is greater than .1   Well, my problem is that when I put it under the group Visibility properties, it does not pull all the data. and if i put it under detail Visibility. It does show me everything but any Part Number that is less than .1 shows just the part number but no detail. How can I make my report show all my data but without leaving data out????
Thanks ahead of time,

View Replies !   View Related
Excel Formula

I have two colums with two different date ranges

1/2/08 11:41 AM


12/27/07 9:38 AM


12/27/07 12:55 PM


1/4/08 11:20 AM

In the third column I'm trying to stated if the dates are different but when i change the first column to show just the date and not the time it still says they are different.  How can i round or  change the first column to just reflect the date and not the time so i can compare it to the second column.



View Replies !   View Related
Sum Formula That Always Adds To Zero
OK, I'm new to Excel 2007 but have used excel for a long time.  I have a column of numbers and I'm trying to add them up.  I click Sum, I highlight to cells I want to add and hit enter and it comes up with zero.  The formula looks fine =SUM(G125:G127) and there are numbers in those cells (that don't add to zero) but it keeps adding them to zero.  Can someone tell me why before I put my fist through my monitor?
When I go in and just click on once cell and then hit + and then another and + and another and + and then hit enter, I get #VALUE.  So there must be something about how the numbers in the cells are formatted??  They look like numbers to me?
Thanks, Peter

View Replies !   View Related

Copyright © 2005-08, All rights reserved