Hi,
I have a date/time field in a SQL2000 database, and what I would like
to do is to filter on a specific part of the field, for example the
time or hour.
Supposing I have a set of data for the last 5 years and would like to
filter out any records which are outside working hours i.e. I would
like to show records where the time is between 9am and 5pm.
Does anyone know if there is a simple way to do this in SQL? If you
simply don't specify the date part in the where clause (e.g. WHERE
issuedatetime between '09:00:00' and '17:00:00') it defaults it to
1900-01-01 so basically no data is returned.
The only way I can see to do this is by using the DATEPART function,
converting it to a varchar, appending 1900-01-01 on to it and
converting it to a datetime, and then using the where clause as stated
above. This is quite a long-winded way, however. Any other suggestions?
I have a field that is stored as a smalldatetime but I want to filter on that field only for the date. How do I ignore the time stamp and only go by the date?
I am trying to generate a report that is basically a class list. It lists classes for a specific semester and lists them by day of the week and then by time.
If I am pulling back a dataset of all classes, how do I filter my report so as to show each set of classes appropriately (by day and by time within the day)? I tried using the Filter function on the table level, but I guess I don't get it.
I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
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?
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'm adding up quantities of an item that are entered in one after another, so the date is the same, but the time differs.
I used the "first" function in access which works the way I want, but sql7 doesn't use the same function. The "convert" function does not work in access but Here is the gist of the query:
SELECT first(CONVERT(varchar,transactions.tran_date,101)) AS [trandate], transactions.tran_type, SUM(transactions.qty) AS totqty,...etc.
I've developed a system that uses SQL Server as the DB backend. I'm having problems saving date/time fields from SOME- not all, workstations. The workstations I'm having problems with are all NT WORKSTATION 4.0, and the native OS language is portuguese. The server is NT SERVER 4.0, and the native OS language is english. All machines have the same international settings, portuguese(Brazilian). SQL Server is english version 6.5.
I've been using the SQL Trace utility to track what's been going on, and I know that on the NT WORKSTATION machines, the dates get sent to the SQL Server in portuguese format, whereas on the other machines, it gets properly sent, in english format. I've read the booksonline, but just don't seem to figure out what and where to configure. Can anyone shed some light into this problem, ie, HOW EXACLTY TO CONFIGURE client workstations in order to get date/time problems solved?
I have a table that I've imported into SQL - there is a field in there for date that must have used now(); as its default value (access).
So the values are something like:
01/11/2004 12:16:42
I need a way to change the data so that the time element removed is the field just holds the date. Failing that a way to insert this from the existing field into a new field stripping the date off en route would be a great help.
We have a piece of software and database for student registers.
One of the biggest problems is the database has been difficult to work with, as I have had to work out the date using dateadd functions and combine fields from different tables.
I've now got the date correctly, but I want to add the time to the time part of a datetime field.
The time field is already stored in a datetime field, but the date in this field is always 1899-12-30.
I am still using SQLv 6.5 and have a need to do monthly reports. It is my understanding that there is no inherent capability in SQL v6.5 for functions such as MONTH getdate() as exist in SQL 2000. Does anyone know of a script that will simulate this so I can extract information from a database such as date_last_update IN MONTH 02?
I m using ASP.NET 2005 with VB,C#. I m using GridView to display the data.
My problem is with the database field date. I want to use only the date and the fields are manufacturing date and expiry date of some items. But,in the gridview it displays the time which is by default same for all the entries which 12:00A.M.
I have used date picker for designing.
What should be the validation for showing only the date and not the time ?? How to hide the time from the gridview. I m using SQL Server 2005 to store database where i have selected the datatype as date/time.
I am using a Ms-Access DS which is accessed by a website's server-side scripts.
What I would like to do is set an existing record's date/time field to null. I have tried to simply alter its value by not including any data within the sharps (##), however that did not work.
Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record. CREATE PROCEDURE UpdateCartItem(@cartItemID Int,@newQuantity numeric(9))ASUPDATE CartItems Set quantity = @newQuantityWHERE cartItemID = @cartItemIDGO
How can I set a column in a table to auto update the date and time everytime something in that row is updated or when the row is first added? Thanks ahead for the help,Jason
How do I extract the ate and time from a datetime field. The field is called Log_DateTime. I want to extract the date and the time and then use these two expressions to sort/filter on in Query Analyzer.
Hi I would like to get more information about using detail view together with auto date/time. I am designing user input form as follow. tid : uid : tool : priority: reticle: status: remarks: request time : <- to autogenetrate current timeInsert Cancel How to use date/time function inside detailed view, insert template.
Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.
I am importing an XLS file into one of my tables. The fields are:
Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2
When I do the import, I get the following:
Date Id Time IO 12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2 12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1 12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
Here are my doubts:
1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages? 2. If I don't combine them, should I use varchar or datetime data type? 2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?
Any comments or suggestions will be very much welcomed.
I am trying to load a table from MS Access into SQL Server. The Table has several columns defined as Date/Time. When I define the transform I get an error saying that the conversion between DT_DBDATE and DT_DBTIMESTAMP is not supported.
