Are There Any Built In Aritmetic Capabilities On The 'Date And Time' Type?
Mar 6, 2006
Simply put, I have a 'Date and Time', (06/03/2006 11:40:00), passed to the SelectCommand via the QueryString. I would like to gain data that is between the supplied time and 1 hour prior.
Is there a simple way to take 1 hr off the 'Date and Time" value or is it necessary to build code that parses the string then adjusts it?
The 'Time' and 'TimeStamp' entities below are both of type 'Date and Time' and all values are gained from the same sql database. I.e. the time used as the basis for selecting the hr period is from the same database as the one where the hour period will be selected from.
Any help would be great.
SelectCommand="SELECT [Timestamp], [Volume] FROM [out8$] WHERE (([CustomerLvl1] = @CustomerLvl1) AND ([Timestamp] = @Timestamp))">
I have a date in my SQL Server Database of 3/12/2204. This is, of course, 200 years after today and is not a correct value.
However, when my repeater is trying to display the records in that table, I get the following error:
"Arithmetic overflow error converting expression to data type datetime."
Here is the stack trace:
[SqlException: Arithmetic overflow error converting expression to data type datetime.] System.Data.SqlClient.SqlDataReader.Read() +157 System.Data.Common.DbEnumerator.MoveNext() +44 System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +504 System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +48 System.Web.UI.WebControls.Repeater.DataBind() +23
Now, none if this is in my own code, and so it is difficult to put any decent error-trapping. This is all in the built-in ASP.NET Repeater and SqlDataReader classes.
Why on Earth would there be an arithmetic overflow here? Note that the original table this is coming from has the date stored in the SqlDbType.DateTime format! In other words, why is there any conversion needed?
Perhaps it is converting the data from SqlDbType to DbType, but even then, what is so difficult about dealing with the year 2204? According to BOL, the DateTime data type should support up to the year 9999!
Note that when I access the table through Enterprise Manager and change the date, the error disappears. I tried a number of over-and-under tests, and I get this error on any date after 3/19/2151!
Does ASP.NET not support dates after that? I doubt it, but does anyone have an explanation for this?
All source and target date fields are defined as data type "smalldatetime". The "select" executes without error though when used with "insert into" it fails with the error:
Msg 295, Level 16, State 3, Line 25: Conversion failed when converting character string to small date-time data type..I am converting from a character string to smalldatetime since the source and target date columns are "smalldatetime". All other columns for the source and target are nvarchar(255). I assume there is an implicit conversion that I don't understand. In a test, I validated that all dates selected evaluate ISDATE() to 1.
USE [SCIR_DataMart_FromProd_06_20_2014] GO IF OBJECT_ID ('[SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]', 'U') IS NOT NULL DROP TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]
I am struggling to calculate Full year in my SSAS Cube. Meaning, regardless of what fiscal year hierarchy level I am in; i need a measure aggregating from 01/01/year of current member to 12/31/year of current member.
I want to replicate it using the Year To Date below:
FY-FQ-FM is the fiscal year quarter hieararchy
I am using for built in time intelligence.
Create Member CurrentCube.[DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date] As "NA"; /*Year to Date*/ ( [DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date],
how to format the last part of this query which comes from Access.:
SELECT Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID FROM (TITLES_WARNING_SENT_qry RIGHT JOIN TITLES ON TITLES_WARNING_SENT_qry.TitleID = TITLES.TitleID) INNER JOIN TITLOC ON TITLES.TitleID = TITLOC.TitleID WHERE TITLES_WARNING_SENT_qry.TitleID Is Null GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID, TITLES.TitleID HAVING (TITLES.RecDT<Date()-31)
I am getting: 'Date' is not a recognized built-in function name.
It is probably something simple but how would I go about converting this part "HAVING (TITLES.RecDT<Date()-31)" to something SQL Server is happy with?
Hi, I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.
My Issue: I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.
Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.
My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.
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,
SELECT (TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
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.
Implement time interval type in the form of a user defined type in SS2k8r2? Specifically an interval type described in the book Temporal Data and the Relational Model by C. J. Date at all. As an example, an interval is below:
1/4/2006:1/10/2006
which would mean the time period from 1/4 to 1/10.
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
INSERT INTO [GPO].dbo.tblMetric (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS) SELECT
1 AS KPI_OWNER_ID , 23 AS METRIC_ID , .75 AS GOAL , CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH , SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS
[Code] ....
The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar. How to get this into the table.
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
Hi Is it possibly in SqlDataSource's SelectCommad to convert a text-type value to a date-type value (11/1/2008 (text) -> 11/1/2008 (date) ) , so that order by can be used. Or is the other ways to use order by correctly with text-type (date)values Thanks.
I am starting on a project where we will be using SSIS. I am totally new to it and would like to ask for your input on whether SSIS can do what I want it to do.
What we are wanting to do is to take data that is loaded into a queue table, and then possibly transform it a bit and load it into a common db as a batch. After that, subsets of that data should then be replicated to other databases based on some criteria. So what we end up with is one database with all the data and two (for now) databases, each with a subset of that data.
Ideally we would want our backend apps to work with the data in the common database and our frontend apps to work with the data in the two other databases.
So I have a couple of questions.
Can SSIS do two-way replication? So if the frontend changed one of the subset databases, could it replicate the change to the common database? And likewise, if the backend changed the common database, could it replicate the change to one of the subset databases?
What is a good way to execute a package from code? For example, from code we would load some tables, and then we would like to execute the SSIS package after that to go through the tables and transform and send that to other tables in one big batch job.
Is it possible to look at the status of package execution from another computer other than the one running the package? I know one way is just for the package to write to a table what it is doing and that table can then be looked at. I was wondering if there was some more elegant way of doing this.
We are also looking at whether we can do our archiving process from SSIS. All it involves is taking the data in the common database and transforming it to xml and outputting it as a file. Is there a way to write out to an xml file? I saw that you can read in xml in SSIS, but I didn't see a way to output xml.
Those are all my questions for now. If you can help me out with even one of them, it would be much appreciated.
What are my limitations with search? Can I search on a phrase? If I search for "bookshelf" will I get everything with 'book' and 'shelf'? Can I search using "and"? Can I sort the results like yahoo does? Can I rank the results? For example, if I search for a product can I sort the results by price?
I'm composing a comparison table of database capabilities of severaldifferent products. I've read the Transact-SQL reference and havegathered most of the information needed for SQL Server 2000 (MSSQL),but there are some things that I'm not very sure of. Could someoneconfirm or complete the points below?* MSSQL can index binary data but only for BINARY & VARBINARY fieldsand not IMAGE.* ALTER TABLE can't rename column name/index name/constraint name.* A functional index (index on expression/function) can be definedusing user-defined function, e.g. CREATE INDEX idx ON t (MY_FUNC(f))* Maximum number of indexes per table?* SEQUENCE (as in PostgreSQL) is not supported, but there is a datatype called TIMESTAMP/ROWVERSION that is 64-bit. SEQUENCE can also beimplemented using a 'counter table'.* Regexp is not recognized, only standard SQL LIKE pattern.* Maximum number of schemas per database?* There is no BOOLEAN data type, but one can use BIT as analternative.* Does MSSQL use a generational/MVCC/record-versioning architecture,to allow writers to not block readers and vice versa?* Can several user-defined functions be created with the same name butwith different signatures (argument type)? For example, REVERSE(text)and REVERSE(varbinary).* Does MSSQL have/use WAL (write-ahead logs)?* Can a unique index contain multiple NULLs?
I have a winform application, where in we get the parameters from the reporting service and we display them in a panel for user's to input their criteria. So far everything worked good, but now we have a requirement wherein dynamic functionality is needed.
For Example:
Based on a selection of a combobox value, we want the other control to be enabled or disabled. Also we want default value of a control to be calculated based on another control's value like .. if user enters value "1" in textbox1 then texbox 2 should have default value of "6". (Textbox1 + 5).........and mix and match of such capabilities.
The Question is........IS IT POSSIBLE??? and if yes...HOW!!!
I've been given a brief to create a web based MI solution to enable users to gain information about their departments and drill down and analyse information presented to them.
I've been asked to look at Reporting services for this, we have been using Crystal Reports, but not to the extent of public facing MI delivery that this project demands.
Could I ask the forum some basic Questions?
1> Is it possible to produce live interactive reports for use on the internet with reporting services.
2> Can I produce reports that can drill down on data, and that can be tweaked so that they are extremly 'user freindly?
3> What are the chart options available, I have been asked to look into providing 'stickmen graphs'. In that a chart is produced to represent a population, and each item of the population is represented by a little man graphic.
4> Are there any good websites that have code snippets and the like to SSRS?
5> Can you recommend any good books.
Thanks in advance for any reponses to these queries.
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example: When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007. When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp 12:00 AM? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp wont appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
My desire is to have my primary db in FL and an online hot standby in both my MI and CA offices. The hardware is in place, as well as T1 size pipes. The solution I'm looking for would have all databases online in case of hardware / environmental failure although the application would only be pointing at the primary (or designated) database server. We have another 3rd party solution available, but I'd like to stay native SQL if possible.
Is replication the way to go?
Does one-to-many work?
Can all the db's be online and available while replicating?
Can I catch up if switched to one of the other db's temporarily?
note:This is 100% SQL 2005.
Thanks -- sorry for the newbie-flavor of the question, but I'm sure a gazillion people have already been down this decision branch.
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
We have a bunch of Audit tables that contain almost exact copies of the operations tables. The audit tables also include:
AuditID - the audit action (insert, modify - old, modify - new, deleted) AuditDate - date and time of action AuditUser - User who did it...
At the end of the day I need to know for any given record what it looked like at the beginning of the day and what it looks like at the end of the day. There could have been numerous changes to the record throughout the day, those records I am not interested in. Only the first record and the last record of a give day.
I am going to be doing a lot of MIN(AuditDate) and MAX(AuditDATE) and .. WHERE AuditDate BETWEEN '10/1/2007 00:00:00' AND '10/1/2007 11:59:59' ...
Question: Whats better for performance:
1. Separating out the date and time and doing a clusterd index on the date.
2. Keeping date and time in the same column and just use a normal index.
