Migrated from Access 2003 to Access 2007. I have an issue now with date fields on an attached Oracle 9i table. After opening the table in Access 2007, then clicking on a date field, it takes about 90 seconds before you can either pick a date or enter a date in this field. This Oracle table is about 176,000 records. A smaller Oracle table of about 3,000 records takes about 2 seconds to access a date field. The problem appears to be only date fields, text fields appears to access imediately. The tables are attached via ODBC, and I have tried to change ODBC drivers from native oracle to MS oracle drivers, with no help. Interesting though, when I drop the table link and re-establish the link, the problem goes away, until I close Access 2007 and re-open it, then the problem re-appears. That is with either ODBC drivers. This issue doesn't exsist on ACCESS 2003 and anyone using ACCESS 2007 here, have the same problem, so it isn't computer related ( the ACCESS 2007 database is network as is the 2003 version).
It's to slow when I handle XML field in SQL 2005! I find that it's slow when I handle XML field (maybe only stored 200kb date) in SQL 2005! Especially when I insert a sub tree to XML field !I have indexed the XML field. Is it possible that the XML field is not ripe in SQL 2005 database?
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed.
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
Hello EverybodyI run the following query against to identical databases. Executiontime on the first DB is 0 seconds, on the other 6 seconds!SELECT dbo.HRMABZ.EMPKEY , dbo.HRMABZ.CONNUMB, dbo.HRM_CALENDER.Datum, dbo.HRMABZ.ABZTXTFROM dbo.HRM_CALENDER INNER JOIN dbo.HRMABZON dbo.HRM_CALENDER.Datum >= dbo.HRMABZ.ABZDATFAND dbo.HRM_CALENDER.Datum <= dbo.HRMABZ.ABZDATTWHERE (dbo.HRMABZ.ABZTECH = 'N')AND (dbo.HRMABZ.ABZLDLT = 'N')AND (dbo.HRM_CALENDER.Valid = 1)I tried to analyze to query execution in the execution plan and foundtwo different arguments in a 'nested loop / inner join' which I dontunderstand. The nested loop does consume most of the time:On the fast Server the argument says:'Outer References:([HRMABZ].[ABZDATT],[HRMABZ].[ABZDATF])'On the slow server the argument says:'WHERE: ([HRM_Calender].[Datum]>=[HRMABZ].[ABZDATF] AND[HRM_Calender].[]<=[HRMABZ].[ABZDATT])'Additional information for the two databases:- Slow database runs on a SQL2000 SP3a English, W2K SP3- Fast database runs on a SQL2000 SP3a German, W2k SP3Can somebody explain me the difference between the two execution plansand how I could force the slow database acting the same way as thefast one does?Thank youThomi
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?
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?
The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID Â Â Â Â Â StartDate 1 Â Â Â Â Â Â 2014-01-01 1 Â Â Â Â Â Â 2014-02-01 1 Â Â Â Â Â Â 2014-05-01-------After Gap Restarted 1 Â Â Â Â Â Â 2014-06-01 1 Â Â Â Â Â Â 2014-09-01---------After last gap restarted 1 Â Â Â Â Â Â 2014-10-01 1 Â Â Â Â Â Â 2014-11-01 2 Â Â Â Â Â Â 2014-01-01 2 Â Â Â Â Â 2014-02-01 2 Â Â Â Â Â Â 2014-03-01 2 Â Â Â Â Â Â 2014-04-01 2 Â Â Â Â Â Â 2014-05-01 2 Â Â Â Â Â Â 2014-06-01 2 Â Â Â Â Â Â 2014-07-01
For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01
My Expected Output id       Startdate 1        2014-10-01 2        2014-01-01
I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want.
The beginning of the SQL query looks like this:
FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦
It is the €œ(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM.
I hope someone out there can help me with making this comparison.
I have a date held in a varchar field in a temporary sql table and I want to convert it into a sql date and it doesn't work. I can replicate this as below -
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
I would like to know whether it is possible to add and updated date column in a slow changing dimension table using the slow changing dimension data flow transformation.
I would like to keep track of what record is updated in the dimension table based on the data being processed.
I want to be able to compare today's date with the date that is in the database. Right now I have:
Select Field1, Field2 FROM table 1 Where Year(TS_Date)=Year('3/1/2006')and Month(TS_Date)=Month('3/1/2006')
Where I have to change the date every month. Is there a way to use GetDate or another type of code so it could automatically update every month. Any suggestions would be very greatful.
I am trying to convert a decimal date value to a date value that I can use in formulas but am having a lot of trouble.My date value currently shows in decimal format YYYYMMDD. I want to convert this to a date so I can then find the number of days between two dates.I have tried convert (datetime, convert(varchar(8),left(qhstdt,8))) with qhstdt as my decimal date field but I receive the error message below:
Error: SQL0204 - CONVERT in *LIBL type *N not found.
I have also tried converting it using (year(QHSTDT)*10000+100*month(QHSTDT)+ day(QHSTDT))) but when I convert the dates using this formula, I can get an incorrect number of days when I try to subtract one from the other.What formula can I use to convert my YYYYMMDD field to a format that will allow me to compare number of days between two dates?
I need help. I'm one step short of getting what I need. Here is what I have: SELECT FilteredIncident.accountidname, FilteredIncident.ticketnumber, FilteredIncident.createdon, FilteredIncident.modifiedon, FilteredIncident.new_enduserfirstname, FilteredIncident.new_enduserlastname, FilteredIncident.responsiblecontactidname, FilteredIncident.statuscodename, FilteredIncident.title, SUM(FilteredActivityPointer.actualdurationminutes) AS TotalTime FROM FilteredActivityPointer LEFT OUTER JOIN FilteredIncident ON FilteredIncident.incidentid = FilteredActivityPointer.regardingobjectid GROUP BY FilteredIncident.accountidname, FilteredIncident.ticketnumber, FilteredIncident.createdon, FilteredIncident.modifiedon, FilteredIncident.new_enduserfirstname, FilteredIncident.new_enduserlastname, FilteredIncident.responsiblecontactidname, FilteredIncident.statuscodename, FilteredIncident.title HAVING (FilteredIncident.accountidname = @accountid) AND ((FilteredActivityPointer.actualstart >= @billtime) OR (FilteredIncident.statuscodename <> N'Problem Solved'))
This pulls everything that started after a certain date (@billtime) AND everything that is not closed. This is what I want; however, I need the SUM(FilteredActivityPointer.actualdurationminutes) AS TotalTime to only calculate the TotalTime after the @billtime regardless of the status. I can easily code in the date in the format 03/04/2008 but I haven't figured out how to do it successfully. I tried an IIF within the SUM and just got a syntax error (I'm assuming it is because FilteredActivityPointer.actualstart is a date/time.)
Essentially, I need a report that shows all open tickets in the system AND all tickets that have been worked on since a specific date regardless of status that only totals the time spent during that date range. Many thanks to anyone who can help.
I have set my record rsAge to a datetime and set default value to (getdate()) I want to fill in a date of birth, when i try this with something like 08/11/78 it just comes back with: 01/01/1900 00:00:00
i wish to set a date data type to a column "Date". when i use datetime format, the values is inserted in the U.S. date & time format. i want the values to be in the british english format. i.e dd/mm/yyyy only and does not require time value. can any one help me to insert the date values in the british english format
Hi,comming from php/mysql some things here on this side are great - but some seems to be solved in a way I can not figure out.What I need is a way to get a string in the format "yyyy-mm" out of a date-time field like:09/05/2006 23:12:36   should produce    2006-09    as one stringWhat I figured out by my own is: SELECT { fn CONCAT({ fn CONCAT(DATENAME(yyyy, dateField), '-') }, STR(DATEPART(mm, dateField))) }, ...but this returns "2006-    9" with blanks in it. Or I could use 2 times the DATENAME but this would give 2006-September.Would it help to use a stored procedure?Thanks,Klaus
Dim komut As String = "Select Fiyat, Tarih from Performans where Stock='" & mystock & "' and Desicion='BUY' and left(mydate,10) >='" & startingdate & "';"where mydate field is described as Date in SQL table and startingdate is calculated also as date format...However this select command does not return the values after a certain date which is given with startingdate... Thanks in advance
I have a table with date fielde.g. SrNo Date 1 1-MAR-2008 2 3-Mar-2008 3 7-Mar-2008 4 10-Mar-2008 5 15-Mar-2008 I need a query to find out Max date which is less than '8-Mar-2008' . i.e i need single output '7-Mar-2008'anybody helpThanks
