How To Add Date Field And Time Field (not Datetime Field )
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
View Complete Forum Thread with Replies
Related Forum Messages:
Informix Date Type Field To SQL Server Datetime Field Error
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. What options do I have that will work?
View Replies !
Combine Separate Date && Time Fields Into One Datetime Field?
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.
View Replies !
Combine Separate Date && Time Fields Into One Datetime Field?
Good morning. 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. Cheers mates.
View Replies !
Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert
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 mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2))) 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. With refresh_shipping_sched .ActiveConnection = CurrentProject.Connection .CommandText = "spRefresh_shipping_sched" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue) .Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option) .Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "") .Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "") Set rs_refresh_shipping_sched = .Execute End Please help
View Replies !
Get The Time From A DateTime Field
I looked at this post and it didn't work for me. Trying to extract the time portion from a datetime field. ex 7:15:12 AM from 1/1/2008 7:15:12 aM This is what I tried and neither functions give me the results I want.... SELECT DueTime, CONVERT(varchar, DueDate, 108) AS Expr1, SUBSTRING(CAST(DueDate AS varchar), 10, LEN(DueDate)) AS Expr2, CAST(DueDate AS varchar) AS Expr3FROM TODO Due Time Expr1 Expr2 Expr3 1/1/2008 5:15:00 PM 00:00:00 12:00:00 AM Jan 1 2008 12:00:00 AM It seems Cast(DueDate as varchar) is the problem it converts the time to the default 12:00:00 AM In the above example I want 5:15:00 PM Tx in advance
View Replies !
How To Get Time Value From Datetime Field?
dear friends.................. i have a table like below.. name date ------------------------------------------------------------------------------------------------------------ anbumani 2/18/2008 4:15:56 PM anbumani 2/18/2008 4:21:29 PM anbumani 2/18/2008 4:23:03 PM anbumani 2/18/2008 4:25:25 PM i want a out put as only time (ex : 4:15:56 PM 4:21:29 PM 4:23:03 PM ) give me the stored procedure code in sql server 2005.. thanks and regards Samuel Chandradoss . J
View Replies !
Create Date Field From Substring Of Text Field
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
View Replies !
How To Compare Time......... Using DateTime Field
hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do...... select DATEPART(hour, loginTime) ......returns me the hour i can get the Hour part of the time but the prblem is how to identify it whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level any ideas??????????
View Replies !
Insert TIME Only In DateTime Field
I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?
View Replies !
Extracting Time From The SQL DateTime Field.
Any help on extracting the time part from the datetime field in SQLdatabase.even though i had entered only the time part in the database when iextract the field it gives me only the date part. i’m using Vb.netdatagrid as a front end.any assistance appreciated!! :?:--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict254266.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877989
View Replies !
Searching A Datetime Field By Time
I have the time of an event stored on each record as a datetime field.Itincludes the year,month,day, etc. Suppose my user wants to search the tablefor all events over the lunch hour, say between11am and 1pm. How do Iconstruct the SELECT query to peek into each datetime field and return onlythose records that satify the specified time range?Many thanks.
View Replies !
Insert Time Into DateTime Field
I would like to be able to insert a time value only into a SQL 7 table colum which has been set as a datetime datatype. When I insert '12:34:44 PM' into the colum it actually inserts '1900-1-1 12:34:44 PM'. An Access table will allow you to insert the time value without adding the '1/1/1900' date value. Can this be done in SQL7? Thanks for any help.
View Replies !
Remove Time From Datetime Field
I have two columns COLUMNA & COLUMNB They both store date & time. In COLUMNA, I would like do delete the time, in COLUMNB, I would like to delete the date. They are stored like this YYYY-MM-DD HH:MM:SS
View Replies !
Saving A Time In A Datetime Field
Hi, I'm trying to save times in datetime fields, but seem to have lots of issues with formats. I'm using a multiline text box, where the user will pick from 09:00 09:30 10:00 etc, so the value that comes out in the .text property is "09:30" Once I have that, whats the best way to convert this into the right format for a dt field? I understand it'll have todays date in it which is fine. It seems to work ok if I overwrite a textbox that's bound to a datetime field, but not if I'm sending it via an insert statement. Also I'm using 24 hour format times, eg 14:00, do I need to do anything to ensure they're store in this format?
View Replies !
Getting Just The Date Or From DateTime Field
Hi, I have used smalldatetime datatype to store my date and time values. i want to store just the date or time but the problem is it stores both the date and time. For eg, if i add the the date 03/11/2004, it also the stores the current time automatically. so the new value will be something like 03/11/2004 10:00:00 AM where i want just 03/11/2004. further problem is even though i managed to store just the date like 03/11/2004 in the database, whole date and time shows up when i display it in my pages. any help will be appreciated. thanx,
View Replies !
Date From Datetime Field
Hi What is the best practice to get the date from a smalldatetime field without the time. The table contains 5 minute readings for energy consumption in the column period. Now i need to get all the readings form some dates. SELECT dbo.TBL_Data.* FROM dbo.TBL_Data WHERE (Period IN (CONVERT(DATETIME, '2003-12-31', 102), CONVERT(DATETIME, '2004-01-01', 102))) this result contains only the readings for the timestamp 00:00 so how to select the whole day ? kind regards piet
View Replies !
Get Date Only From Datetime Field!
OK, I know this is really simple but I can't get my statement to pull just the date from a datetime field! Here's my query: select * from tblPR where date between convert(datetime, dateadd(day,-day(getdate())+1,getdate()),103) and convert(datetime, dateadd(day,-day(getdate()),dateadd(month,1,getdate())),103) I get no errors but I get the timestamp too and I only want the date. Where am I going wrong?! Thanks in advance, Brett
View Replies !
Compare Date Field To Text Field
Hi, 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 appreciate your help. Bill
View Replies !
Ignore Time Portion Of Datetime Field
I have a PHP page where the user enters a date that represents the last day of a timesheet (ts_end) and the hours worked on that timesheet. That is then written into a table where the date is a datetime type. Because the user just enters a date, the time portion of the field is set to 00:00:00. In another place, I need to sum the columns for reports submitted between the beginning of a timesheet (ts_end -6 days) and the ts_end date. The problem is that chartreviewed values entered on the ts_end date are getting lost because the time part of the ts_end field is 00:00:00 and the time part of the dateentered for the chartreviewed value is not. For instance using 2/4/2004 as the ts_end date looses the 192 charts. Reporter activity chartsdateentered 2001576 20672 563 2004-01-29 13:55:51.000 2001576 20665 202 2004-02-02 19:54:57.000 2001576 20666 160 2004-02-03 22:48:11.000 2001576 20667 192 2004-02-04 19:41:51.000 I know I can revise the query to look for charts where the dateentered is less than dateadd(d,1,ts_end) and get the right values. It seems like there has to be a way though to tell sqlserver to ignore the time part of a datetime field when querying. Anybody know what it is? Thanks, Ursus
View Replies !
How To Test The Time Part Of A DateTime Field
Dear all, I have an application where I need to implement a Team Shift management. The team shift table need to be defined as follow : Shift table ========= Id DateTime IsCurrent 1 08:00:00 True 2 16:00:00 False 3 00:00:00 False From the table above the current running shift is Id = 1 Based on that I have following question: 1 - Is there a way to specified for a DateTime field that it will recieved only Time part ? 2 - When a shift change occurs, I need to compare the Current time with the time shift in the table. Then according to that I set the IsCurrent flag to proper new Shift ID.. how to perform this ? Thnaks for your help regards serge
View Replies !
How To Retrive Date(alone) From Datetime Field
hai In my web application i want to bind data from sql 2005 to ultrawebgrid, when i use source code dim cmdselect as sqlcommand dim cmdstring as string cmdstring="select name, datefieldname from tablename" cmdselect=new sqlcommand(cmdstring,connectionstring) connectionstring.open() ultrawebgrid1.datasource=cmdselect.executenonquery() ultrawebgrid1.databind() connectionstring.close() ----- when i execute above coding i am geting date and time displayed there, but i want to display date alone --my datefield datatype is datetime - i am using sql 2005,(vs2005-vb/asp.net) please help me thanks in advance
View Replies !
Select Only Date From Datetime Field
I know there must be a way to do this.... I want to select only the date portion of a datetime field. the date normally shows as similar to '2004-01-01 09:39:52.000' I need to return just '2004-01-01'. using convert and cast returns the dates as 'JAN 01 2004' - this won't sort correctly, so it is of no use. any suggestions? thanks, john
View Replies !
Update Time Portion Of The Datetime Field In Sql Table
I have two table I like to change startdate time to 00:00:00.00 and Enddate time to 23:59:59.000 Table 1 ID Startdate Enddate 418 2008-04-28 05:00:00.000 2008-05-04 05:00:00.000 419 2008-05-05 05:00:00.000 2008-05-11 05:00:00.000 420 2008-05-12 05:00:00.000 2008-05-18 05:00:00.000 421 2008-05-19 05:00:00.000 2008-05-25 05:00:00.000 422 2008-05-26 05:00:00.000 2008-06-01 05:00:00.000 423 2008-06-02 05:00:00.000 2008-06-08 05:00:00.000 424 2008-06-09 05:00:00.000 2008-06-15 05:00:00.000 425 2008-06-16 05:00:00.000 2008-06-22 05:00:00.000 426 2008-06-23 05:00:00.000 2008-06-29 05:00:00.000 427 2008-06-30 05:00:00.000 2008-07-06 05:00:00.000 428 2008-07-07 05:00:00.000 2008-07-13 05:00:00.000 429 2008-07-14 05:00:00.000 2008-07-20 05:00:00.000 430 2008-07-21 05:00:00.000 2008-07-27 05:00:00.000 431 2008-07-28 05:00:00.000 2008-08-03 05:00:00.000 432 2008-08-04 05:00:00.000 2008-08-10 05:00:00.000 433 2008-08-11 05:00:00.000 2008-08-17 05:00:00.000 434 2008-08-18 05:00:00.000 2008-08-24 05:00:00.000 435 2008-08-25 05:00:00.000 2008-08-31 05:00:00.000 Table 2 ID Startdate Enddate 445 2008-11-03 06:00:00.000 2008-11-09 06:00:00.000 446 2008-11-10 06:00:00.000 2008-11-16 06:00:00.000 447 2008-11-17 06:00:00.000 2008-11-23 06:00:00.000 448 2008-11-24 06:00:00.000 2008-11-30 06:00:00.000 449 2008-12-01 06:00:00.000 2008-12-07 06:00:00.000 450 2008-12-08 06:00:00.000 2008-12-14 06:00:00.000 451 2008-12-15 06:00:00.000 2008-12-21 06:00:00.000 452 2008-12-22 06:00:00.000 2008-12-28 06:00:00.000 453 2008-12-29 06:00:00.000 2009-01-04 06:00:00.000 454 2009-01-05 06:00:00.000 2009-01-11 06:00:00.000 455 2009-01-12 06:00:00.000 2009-01-18 06:00:00.000 456 2009-01-19 06:00:00.000 2009-01-25 06:00:00.000 457 2009-01-26 06:00:00.000 2009-02-01 06:00:00.000 458 2009-02-02 06:00:00.000 2009-02-08 06:00:00.000 459 2009-02-09 06:00:00.000 2009-02-15 06:00:00.000 460 2009-02-16 06:00:00.000 2009-02-22 06:00:00.000 461 2009-02-23 06:00:00.000 2009-03-01 06:00:00.000 462 2009-03-02 06:00:00.000 2009-03-08 06:00:00.000
View Replies !
Update Time Portion Of The Datetime Field In Sql Table
I like to add a day to this date and also make time to 23:59:59. So end result for this table and recrods will be 2008-11-09 23:59:59 and next row 2008-11-16 23:59:59 so on..... 2008-11-08 23:00:00.000 2008-11-15 23:00:00.000 2008-11-22 23:00:00.000 2008-11-29 23:00:00.000 2008-12-06 23:00:00.000 2008-12-13 23:00:00.000 2008-12-20 23:00:00.000 2008-12-27 23:00:00.000 2009-01-03 23:00:00.000 2009-01-10 23:00:00.000 2009-01-17 23:00:00.000 2009-01-24 23:00:00.000 2009-01-31 23:00:00.000 2009-02-07 23:00:00.000 2009-02-14 23:00:00.000 2009-02-21 23:00:00.000 2009-02-28 23:00:00.000 2009-03-07 23:00:00.000
View Replies !
What Default Date To Use In Database Datetime Field
We want to add a default date to our database tables. Looking at other database samples people use all sorts of dates to add as default date e.g. 1/1/1997 or the getdate() function. Is it good practice to set a default date and what should the default date be???? Newbie
View Replies !
Problem With Inserting Date In A Datetime Field
Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer. That's my problem: if the server is in english version, I have to insert date with this code: DateTime.Today.ToString("MM/dd/yyyy") instead if the server is in italian version, I have to insert date with this code: DateTime.Today.ToString("dd/MM/yyyy") Is there a way to insert a date in standard way, without knowing the server version? bye and thanks in advance
View Replies !
Date Poition Comparison Of A Datetime Field
I have a datetime variable coming from my ASP.NET application that hasa time portion. I give my users the option to perform an equals,greater than, less than, or between comparison. The trouble comes inthe way the application builds the criteria string. The WHERE clausepassed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".What I want to do is only compare the date portion of start_dt to thedate portion of the passed in time. Manipulating the start_dt with thebuilt-in SQL functions isn't a problem, but altering the date passed infrom the ASP.NET would be a massive framework change in the app.Is there any way to only compare the date portions of both the SQLfield and the passed in value?Thanks.
View Replies !
Strange Out Of Range Date In A Datetime Field
How is that I have stuff like... 13415-10-14 72:00:56.973 and -21858-02-28 390:54:27.200 in a datetime field when BOL says... "Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds." Not only that but I can't find where this field is getting written to in my trace
View Replies !
Date / Time Field
Dear Frends Iam using SQL Server 2000 Is there any way to use Date time field to store value before the Date 01-Jan-1753? Thanks
View Replies !
SELECT From DateTime Field Based On Current Date
I am trying to match records that are >= the current date. I have tried using: SELECT DISTINCT name FROM table WHERE datefield >= DATEPART(month, GETDATE()) AND datefield >= DATEPART(day, GETDATE()) AND datefield >= DATEPART(year, GETDATE()) ORDER BY name but this is not giving me the result that I am looking for. What is the best way to match a DateTime field type using the current date without the time?
View Replies !
Looking To Collect Distinct Date Part Out Of Datetime Field
from this, circdate being a datetime field:SQLQuery = "select distinct circdate from circdata order by circdate"I need the distinct date portion excluding the time part.this has come about when I discoveredI am inserting and updating some datetime values with the same value,but for some reason, the values are always off by a few seconds. I seta variable called SetNow assigned to NOW and then set the datetimefields to this SetNow variable. Then when I collect the distinct datetime I am assuming they will have the same values recorded incircdate, but no, they are off by several seconds. Makes no sense to meat all. I tried renaming the variable several times but it makes nodifference at all.any help appreciated, thanks.
View Replies !
Strip Time Out Of A Date Field - Sql
Hi all, 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. Many thanks Phil
View Replies !
Date/time Field Problems
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?
View Replies !
Grouping Date Field By Day Only, Not Time
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. thanx, Herb
View Replies !
How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?
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? Please advise. Thanks. Best. K7
View Replies !
SUM If Date Field &&>=inputted Field?
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.
View Replies !
Want To Hide The Time From The Date Field In GridView In ASP.Net With SQL
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.
View Replies !
Filtering On Parts Of A Date/time Field
Hi,I have a date/time field in a SQL2000 database, and what I would liketo do is to filter on a specific part of the field, for example thetime or hour.Supposing I have a set of data for the last 5 years and would like tofilter out any records which are outside working hours i.e. I wouldlike 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 yousimply don't specify the date part in the where clause (e.g. WHEREissuedatetime between '09:00:00' and '17:00:00') it defaults it to1900-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 andconverting it to a datetime, and then using the where clause as statedabove. This is quite a long-winded way, however. Any other suggestions?Thanks,Matt
View Replies !
Setting A Date/time Field To Null
Hello! 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. How can I accomplish this? Thank you! Dave
View Replies !
Format Date/time Field In Ssrs
hi all I have two date/time fields as below: =(Fields!ClosedDate.Value)-(Fields!CreatedDate.Value) result can sometimes look like this 14:01:42.3840000 how do I format this I have tried =FormatDateTime((Fields!ClosedDate.Value)-(Fields!CreatedDate.Value), 2) but this gives an error I want the result to look like this 14:01:42.38 thanks Dianne
View Replies !
DTS Import Error For Flat File Time Data Into SQL 2000 Datetime Field
The DTS Import Data tool (SQL 2000 sp4) imports time field data from a text file into a table field (with datetime Data Type) using a base date (or zero integer date) of 12/30/1899 rather than the expected value 1/1/1900. Is this a known issue or an error on my part? My results: I created the following table structure: CREATE TABLE [Test] ( [Date] [datetime] NOT NULL , [Time] [datetime] NOT NULL , [Price] [smallmoney] NOT NULL , [Size] [smallint] NOT NULL , [DatePlusTime] AS ([Date] + [Time]) ) ON [PRIMARY] GO I imported the following text file with the DTS Import tool: Date, Time, Price, Size 12/21/2005, 07:52:27, 1.1926, 1 12/21/2005, 07:52:41, 1.1928, 1 12/21/2005, 07:52:49, 1.1927, 1 Using EM Console Open Table I got the following display: Date, Time, Price, Size, DatePlusTime 12/21/2005, 7:52:27 AM, 1.1926, 1, 12/19/2005 7:52:27 AM 12/21/2005, 7:52:41 AM, 1.1928, 1, 12/19/2005 7:52:41 AM 12/21/2005, 7:52:49 AM, 1.1927, 1, 12/19/2005 7:52:49 AM The Time field displayed here would indicate a zero-base date (zero integer) value of 1/1/1900 for the date portion of the data stored in the datetime data type field. However, the DatePlusTime field shows this to be incorrect since Date + Time do not properly combine to the same date. Rather they combine to a date two days earlier. Using Query Analyzer I got the following display: Date, Time, Price, Size, DatePlusTime 2005-12-21 00:00:00.000, 1899-12-30 07:52:27.000, 1.1926, 1, 2005-12-19 07:52:27.000 2005-12-21 00:00:00.000, 1899-12-30 07:52:41.000, 1.1928, 1, 2005-12-19 07:52:41.000 2005-12-21 00:00:00.000, 1899-12-30 07:52:49.000, 1.1927, 1, 2005-12-19 07:52:49.000 Sure enough, DTS has converted the text time fields to a datetime value with what appears to be a €“2 integer value (date of 12/30/1899) rather than a 0 integer (date of 1/1/1900). This accounts for the DatePlusTime formula (Date+Time) yielding a date two days earlier than the date field. As a work-around I could always copy date and time into character fields and use VB to create a proper datetime value for each record. However, I am wondering if there is a more direct fix that I am missing. Would this be a bug with a patch that I missed or is this just an anomaly (known or undiscovered) that I have to live with?
View Replies !
Problem With Current Date For Date & Time Field
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
View Replies !
|
TRACKER
