I just uploaded my tables to sql server, and am using access to run the forms. In one listbox I have a few fields that I want to display only the time ex. (9:00pm) which works good in access, but in sql the listbox displays the date + the time (04/01/2004 + 9:00pm).
The datatype I am using is datetime length 8.
I also have a strange problem with modifying records. In access it takes one click on my "modify record" button I made to change a field in a record, but in sql it now requires two clicks. Anyone know what could cause this?
I have various reports which have in the header a textbox with a time/date expression.
e.g. =Format(Now, "HH:mm on dddd, d MMMM yyyy")
These reports have a daily snapshot taken so users can look up historic versions of reports. The problem is that if the PDF renderer is used the time displayed is the time now not the time the data was generated. (If I view the snapshot in the browser the correct historic time and date is shown though.)
This is at best confusing and at worst could lead to some bad business decisions being made.
Is there any way I can get it to display the actual time and date the snapshot was generated?
I have tried replacing "Now" with "Globals!ExecutionTime" with no effect.
I have also seen a suggestion to move the header textbox into the report body and then add a new textbox to the header pointing at the textbox in the body
e.g. with Expression =ReportItems("LabelToday").Value
But this does not work either and it is still displaying the time now when viewed in PDF format.
Is there any straight forward way of doing this?
If there is no straight forward way can I somehow access the HistoryID from within Report Code so I can look up the correct value myself in the ReportServer database?
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.
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.
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
This one I hope is easy, but I've been working late and it seems to be aluding me. I have a field called time_in which is a char(6) field type. This field holds time elements such at 10:57
My query below is substracting one hour from Time_In and returning it as Hold_Time. I only want to return the time element without the date portion or the milliseconds, etc.
Code Block select substring(cu.time_in, 1,5) as time_in, DATEADD(hour, -1, substring(cu.time_in, 1,5)) as hold_time from reports.careunits_hold_hours cu
The stored procedure takes ~ 10 seconds every call. The execution plan is Index seek or clustered index scan almost everywhere . No table spools or lazy loads or key lookups. Cannot share the code or execution plan due to company rules and regulations.Issue is that i am dealing with result set of around 50,000 records . Out of these i have to return 20 records at a single time (which is also customizable i.e 40 / 60/ 150 records in a page). Application cannot handle all 50k records so i have to return 20 records for every stored procedure call.
The result set changes as per the start date and end date which i recieve as parameters. In application there are few Column filters namely- Country(around 50 countries), Outcome(around 6 to 10 values ) . These filters will values in drop down( as excel ) depending on the distinct values in that columns. These filters will be populated on every page, if no filter value is selected.Issue is if user does sorting or filtering any records , this stored procedure is called and every time i have to deal with ~50000 records.
Current Code : Step 1 ) Get the required result set in temp table . Step 2) Compute the results on some business rules . (Outcome and SharesAvailable calculation - see attachment) Step 3) populate filter Columns (Country,Outcome) these values will be comma seperated. Step 4) Dynamic query to get required result set i.e if user wants only 10 records in single page then TOP 10 . Sorting can be applied on any column mentioned in screenshot.
I know I can display the time that the report was generated, but can I display how LONG it took for the report to complete?
From when the first query was executed to the time all the data was returned and/or the time it took for the report to be rendered back to the end user?
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.
I have the following code <html> <head> <title>Eye Contact - Century to Century</title> <link rel="stylesheet" href="eye01.css" type="text/css" /> </head> <body> <form name ="" method = "post" action=""> <table width="100%" border="1" height ="85%"> <tr height = "15%"> <th>Date</th> <th>Year Left</th> <th>City Origin</th> <th>Country Origin</th> <th>Year Arrived</th> <th>City Arrived</th> <th>State Arrived</th> <th>Filename</th></tr>
<?php $db = mysql_connect("localhost","root",""); if (!$db) { print "error - Could not connect to MySQL"; exit; }
$er = mysql_select_db("touch_art1"); if(!$er) { print "error - Could not select the database"; exit; }
extract($_POST);
$query = "SELECT date, yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, filename FROM story WHERE country_origin = '$search_country' AND pamapproved = 'Y' ORDER BY yr_left"; $result = mysql_query($query);
How would I display just the date in the dataset if I have the data with date and time like this 2007-05-02 07:14:48.000 I want to truncate/or something to the time and just display the date. How would I change my query.
SELECT usr_end_date,date_exp,usr_last_name, usr_first_name, usr_login,employeeid, displayname FROM [OIM_FIX].[dbo].[OIM_USR] a, CORP_EMP_IDs b WHERE USR_login = CAST(b.employeeid AS varchar)and usr_end_date <> date_exp
currently it is returning the full date and time like this 2007-05-02 07:14:48.000 I want just the date like this 2007-05-02
I have a table in SQL Server 2000 with have date/time columns anddisplays the dates as : 1900-01-01 00:00:00.000 which is fine, but whenI select the column through dreamweaver for an asp page to date isshortened to 1900-01-01 but I need to see the whole date / time fieldfor conversion purposes.Can anyone help ?Thanks in advance.
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.
I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest; USE SpecTest GO
CREATE TABLE [dbo].[Spec1]( [Spec1ID] [int] IDENTITY(1,1) NOT NULL, [Channel] [int] NOT NULL,
ExecutionTime gives server time, How can I get local time ? I'm planning to create a report part and use it across all the reports to display report execution time.
- Reports are being opened in Citrix -MS SQL Server 2008 R2
When I do a select on a date field, the date in 6.5 was displayed as- Jan 1 1999 12:05AM
In 7.0 it is displayed as- 1999-01-01 00:05:00.000
Can anyone please shed some light on this? ie. why is there this difference? Also can this display format be controlled (by server language used or regional date setting)?
