Date Between Two Dates And Sum Of A Second Field
Jun 25, 2012
I'm just getting into using sql server 2008 and the report builder 3.0 addon after a long time away from sql and I was wondering if the following was possible.I have a number of records with a start and end date along with a volume i.e.
Delivery Start; Delivery End; Volume
01/06/12; 05/06/12; 25,000
01/06/12; 02/06/12; 50,000
24/05/12; 26/10/12; 5,000
Essentially this means for each day between the two delivery dates the stated volume is delivered, what i need to try and do is to find the volume for each day between two user entered dates, giving a result along the lines of
Start Date Entered = 01/06/12
End Date Entered = 03/06/12
Date; Total Volume
01/06/12; 80,000
02/06/12; 80,000
03/06/12; 30,000
Is this actually doable?
View 4 Replies
ADVERTISEMENT
Jan 11, 2006
hello
how can i select all dates upto todays date and include the first next future date in a given data base
say todays date was the 01/06/2006 (MM,DD,YYYY)
below is a mock data base
id date (MM,DD,YYYY)
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
5 01/20/2006
i want to select all dates equal or less that 01/06/2006 and include the first next future date .. and in this case it would be 01/09/2006
so the results would return
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
View 2 Replies
View Related
May 4, 2006
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 3 Replies
View Related
Mar 19, 2007
Hello, this is my first post to these forums so... Hi.
I'm currently doing a select statement and I cannot figure out how to get it to do what I want it to.
I'm trying get the statement to show how much activity was done in each year in a separate field and I don't have any idea where to start to get this accomplished.
Here's the code:
select distinct client.Number as [ClientNumber], client.FirstName, client.LastName,
client.Address, client.City, client.State, client.Zip,
count(distinct [transaction].date) as VisitsWithinDateRange, client.LastVisit
from client
inner join [transaction] on client.number = [transaction].client
where
[transaction].date >= '01/01/1980'
and
[transaction].date <= '01/01/2008'
and
client.status like '%a%'
and
client.status not like '%n%'
and
client.status not like '%i%'
and
client.clienttype <> 'C'
group by client.number, client.Number, client.FirstName, client.LastName,
client.Address, client.City, client.State, client.Zip, client.LastVisit
View 3 Replies
View Related
Dec 30, 2003
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 6 Replies
View Related
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE"Â .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Oct 17, 2007
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 1 Replies
View Related
Aug 3, 2007
I have a form that's filtering search results and porting to a gridview. The drop down is selecting from the date column of SQL, but i want it the default value to show all dates in the gridview. In a normal string field, you can just use "%", but a datetime field won't take this. What can i use to show all dates?
View 3 Replies
View Related
Jul 30, 2007
Hello all,
This is the best suited forum I found that fits my problem. If there's a better one, please direct me to it.
I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables).
My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device).
I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates.
That way I can only compare relevant data and not the whole database.
For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007.
Another example: Filter out rows that their Address column was modified in the last two weeks.
I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables.
But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.
Is there a chance this is implemented inside Access (or SQL server, for that matter)?
The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.
Any help will be greatly appreciated!
Thanks.
P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.
P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)
View 18 Replies
View Related
Aug 9, 2007
I have a table with several million rows of data. There is a date field defined as a char(8) with some bad rows. i tried to locate them with below
select date_stopped from patient_medication
where isdate(convert(datetime,date_stopped)) = 1
This won't work, I get the
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Any way around this?
View 2 Replies
View Related
Jul 30, 2007
Hello all,
(This was previously posted @ http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1935938&SiteID=1&mode=1, I transferred it here because it's more relevant)
I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables).
I use C++ non-.NET ADO in order to access that database (msado15.dll).
My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device).
I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates.
That way I can only compare relevant data and not the whole database.
For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007.
Another example: Filter out rows that their Address column was modified in the last two weeks.
I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables.
But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.
Is there a chance this is implemented inside Access (or SQL server, for that matter)?
The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.
Any help will be greatly appreciated!
Thanks.
P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.
P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)
View 2 Replies
View Related
Jul 20, 2005
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 2 Replies
View Related
Feb 25, 2008
I am doing a search on a column of type varchar, the columns all contain dates in the future. When i perform a query
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
,[CIFExpDate]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE CAST([CIFExpDate] AS smalldatetime) between '12/01/2000' and '01/30/2015'
I get error:
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
I can use this same query on an exactly same other field that contains dates in the past. Is there some kind of limitation on the dates as far as them being in the future or past?
View 4 Replies
View Related
Aug 31, 2014
I have two tables, one contains a list of month end dates and one contains a list of resource requirements. I want to show the resource required by month, where the month end date falls between the start and end of the resource requirement date. So far I have:
Select Sum(tblResRequirements.FTE) As RequiredFTE,
vwMonthEnds.MonthEnd
From tblResRequirements,
vwMonthEnds
Inner Join on MonthEnd between StartDate and EndDate
Group By vwMonthEnds.MonthEnd
Which isn't working as the Join is not correct
What is the right code to use to get the join to work?
View 5 Replies
View Related
Nov 13, 2007
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 1 Replies
View Related
Jul 6, 2015
A date field in a report returns the date value as:
2015-07-01 13:30:27.000
Is there any way I can script this to appear as:
01 July 2015 (or 01-07-2015 or 01/07/2015)Â - basically to cut out the hours, minutes and seconds?
The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:
01 July 2015 13:30
View 9 Replies
View Related
Jun 18, 2007
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.
View 1 Replies
View Related
Jun 2, 2014
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.
Is this even possible?
View 3 Replies
View Related
Apr 23, 2015
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
View 4 Replies
View Related
Sep 27, 2005
Hi,
I need to convert a var char field to date field (DD/MM/YYYY)
Current convertion format:
CAST(Report_Date as DATETIME)
How can i convert to Date field with date format of DD/MM/YYYY?
Thanks.
Onn Onn
View 2 Replies
View Related
Mar 27, 2008
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 8 Replies
View Related
Oct 8, 2005
Is there a way that I can get a resultset that contains unique dates ina given date range without the need to have a temporary table and acursor?perhaps something like:declare @start_date as datetimedeclare @end_date as datetimeset @start_date as '1/1/2005'set @end_date as '1/1/2006'select fn_getuniquedate(@start_date, @end_date)1/1/20051/2/20051/3/2005...12/31/2005
View 24 Replies
View Related
Mar 16, 2014
I want to display only the date part of a date field which contains both date & time information.
For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.
I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.
View 3 Replies
View Related
Jul 14, 2014
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 -
So I run
select
cast ('14/02/2014' as date)
and I get a conversion failed error.
View 2 Replies
View Related
Jul 4, 2014
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
View 1 Replies
View Related
Dec 29, 2005
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 7 Replies
View Related
Nov 3, 2006
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies
View Related
Feb 26, 2008
Hi,
I need to insert into a column (lets say column x) a date based on the date on another column (lets say column y).
What I need is:
Take the day and month from column x (all records are formated yyyy-mm-dd)
Place it in column y
The yyyy in column y should be - currenct year +1 and no the year in column x.
All help welcome.
View 9 Replies
View Related
Jun 23, 2005
Hi guys.
I have trouble inserting dates, in my database, which is set to "datetime".
What i am trying to insert is this: "dd-mm-yyyy HH:MM:00"
Which is eg. "22-06-2005 17:56:00"
It wont accept this format im using
Why?
View 2 Replies
View Related
Feb 8, 2006
Hi,
I have the following table:
Table name Employee
===============
emp_id,
emp_name,
emp_city
Table name EmpStatusReport
===================
emp_id,
action
date
I need to write a sql query to get the emp_name, emp_city and the recent date when the user has sent status report over last 30 days. The user has sent a status report if action field in the empStatusReport is set to 'reported'. This table gets filled everytime user sends report.
I tried to the do the following:
select e.emp_name, e.emp_city, esr.date from employee e, EmpStatusReport esr where e.emp_id and esr.emp_id and
/* esr.date = max(esr.date) and esr.date > currentDate - 30 and esr <= currentDate */
I am not able to write a correct login for date part. Any help in this will be highly appreciated.
Thanks!
View 4 Replies
View Related
Dec 10, 2014
I have two dates. How do I get the one that is the lowest. One may be null. I don't want null unless they are both null
I tried..
DECLARE @Handle date
SELECT @Handle = dbo.getTrkLeastDate('2014-12-09',NULL)
print @Handle
ALTER FUNCTION [dbo].[getTrkLeastDate] (@d1 date, @d2 date)
RETURNS datetime
[Code] .....
View 4 Replies
View Related
Jun 18, 2014
I have a field called 'LOG_COMMENTS' in a table named T_PRODUCTION_WORK_LOG.
In the 'LOG_COMMENTS' whenever a request is placed on hold comments are added by the application, such as 'Status changed from Open to On Hold' and 'Status changed from On Hold to Open' along with a 'LOG_DATESTAMP' field. A request can go on and off Hold multiple times, how do I determine the days a request is On Hold?
I know I can use the sql function DATEDIFF ( datepart , startdate , enddate ), but how do I account for the possiblity that the request was On Hold more than once? And how would I get LOG_DATESTAMP' times for 'LOG_COMMENTS' that contain 'Status changed from Open to On Hold' and 'Status changed from On Hold to Open''?
View 7 Replies
View Related
Jul 6, 2006
I am currently working in the sql server 2000 environment and I want towrite a function to pull all dates within a given date range. I havecreated several diferent ways to do this but I am unsatisfied withthem. Here is what I have so far:declare @Sdate as datetimedeclare @Edate as datetimeset @SDate = '07/01/2006'set @EDate = '12/31/2006'select dateadd(dd, count(*) - 1, @SDate)from [atable] vinner join [same table] v2 on v.id < v2.idgroup by v.idhaving count(*) < datediff(dd, @SDate, @EDate)+ 2order by count(*)this works just fine but it is dependent on the size of the table youpull from, and is really more or less a hack job. Can anyone help mewith this?thanks in advance
View 3 Replies
View Related
