SELECT And Dates Question

Sep 30, 2007

My table contains three records as an example

item boxes quantity date
CH 20 16 10/09/2007
CH 10 20 11/09/2007
CH 15 16 12/09/2007

using the request:

SELECT item, sum(boxes), quantity FROM transactions WHERE (item = 'CH' AND date_recvd BETWEEN '09-09-2007' AND '12-09-2007') GROUP BY item, quantity

I get:

CH 35 16
CH 20 10

but if I change my date parameters to:

SELECT item, sum(boxes), quantity FROM transactions WHERE (item = 'CH' AND date_recvd BETWEEN '09-09-2007' AND '10-09-2007') GROUP BY item, quantity

I still get:

CH 35 16
CH 20 10


My query syntax is obviously incorrect. What should it be please?

View 5 Replies


ADVERTISEMENT

Date Select Query - Select Between Two Dates

Aug 22, 2006

have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.

i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')

when i run this query i get this message

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.

the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15

please help
mustfa

View 6 Replies View Related

Select Dates Using BETWEEN?

Nov 5, 2007

How would I go about selecting a datetime field for a date range?

e.g.

$start_date = 04/11/2007
$end_date = $05/11/2007

I would have thought it would be something like this:

SELECT * FROM order_details BETWEEN $start_date AND $end_date

BUt I cannot get it to work in MS SQL? Would it be anything to do with the data stored such as 5/11/2007 12:00:00 AM

View 8 Replies View Related

Select With Max And Min Dates

Nov 22, 2014

I´m struggling to get the following query:

Sales for the Last day of the month – sales for the last day of the previous month

Grouped by type and by month

DECLARE @sales TABLE
(type VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
sales NUMERIC(10, 2) NOT NULL);
INSERT INTO @sales(type, date1, sales)

[Code] ....

View 1 Replies View Related

Select Parameters With Dates

Jan 16, 2007

I have a sqlDataSource control that has its select command and parameters set dynamically.  One of the parameters is a date, which should be in smalldatetime format (ie, 12/22/2006).  Obviously, using SQL Server, the data is stored with both date and time.  The problem is, I cannot get any data selected.   I initially thought that I needed to tell it to only look at the date and not the time, but from what I have seen this should work. Here is the pertinent code. 
dim seldate as datetime
selDate = Session("ChosenDate")
Dim SelCmd As String = "SELECT [PatientName], [AssignedTo], [CPT], [CPT2], [HospitalID], [RoomNbr], [ACType], [TxDx1], [TxDx2], [MRNbr], [Notes], [PatientID], [PCPID], [ResidentID], [Status], [CaseID], [AdmitDate], [crtd_datetime] FROM [DailyBilling] WHERE ([crtd_datetime] = @crtd_datetime) and ([AssignedTo] = @AssignedTo) and ([HospitalID] = @HospitalID) ORDER BY [PatientName]"
SqlDataSource1.SelectCommand = SelCmd
SqlDataSource1.SelectParameters.Clear()
 
 
Dim parFilterProvider As New ControlParameter()
parFilterProvider.ControlID = "ddlProvider"
parFilterProvider.Name = "AssignedTo"
parFilterProvider.Type = TypeCode.String
SqlDataSource1.SelectParameters.Add(parFilterProvider)
 
 
Dim parFilterHospital As New ControlParameter()
parFilterHospital.ControlID = "ddlHospitals"
parFilterHospital.Name = "HospitalID"
parFilterHospital.Type = TypeCode.String
SqlDataSource1.SelectParameters.Add(parFilterHospital)
 
 
 
Dim parFilterStatus As New Parameter()
parFilterStatus.Name = "crtd_datetime"
parFilterStatus.DefaultValue = selDate.ToShortDateString
parFilterStatus.Type = TypeCode.DateTime
SqlDataSource1.SelectParameters.Add(parFilterStatus)
 
Gridview1.databind()
Any ideas?

View 5 Replies View Related

SQL Select Help Needed - Dates

Mar 15, 2004

Hi everyone,

I've got a problem that I've been unable to work out.

I'm wanting to count the number of people that fall into a specific age group. For simplicity, lets say I'm JUST after people that are 10 years old.

This is what I'm trying to do:


SELECT 'Aged 10 years old', count(*)
FROM [People]
WHERE ([DOB] + [Date Entered]) = ?5 years?


Does anyone have a solution to this?

Thanks
Andrew

View 2 Replies View Related

Select All Records Between 2 Dates

Apr 6, 2004

I am trying to select all records added between 2 dates that the user inputs into a form and am having problems. I had this working no problems with asp but can't seem to get it working with .net. BTW I am using SQL Server and Visual Studio.

The asp.net code I am trying to use is:


Me.SqlSelectCommand1.CommandText = "SELECT news_title, news_date, news_type, news_link FROM news WHERE (news_type = 'news') AND (news_date BETWEEN CONVERT(DATETIME, '"" & startdate & ""', 102) AND CONVERT(DATETIME, '"" & enddate & ""', 102))"

....

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim startdate As DateTime
startdate = Request.Form("date_from")

Dim enddate As DateTime
enddate = Request.Form("date_to")

SqlDataAdapter1.Fill(DataSet1)
Repeater1.DataSource = DataSet1
Repeater1.DataBind()

End Sub


With this I am getting the following error:

"Syntax error converting datetime from character string. "

So I am assuming it is something to do with the way I am getting the date from the form as when I hardcode the dates in it works???

Any help would be greatly appreciated, thanx

View 1 Replies View Related

Select With Consecutive Dates

Jul 30, 2012

I have a table with

EmpNum, Date, Abstype

What I want is to pull a list of all the employees and the Monday date of employees who have an absence on a Monday --> Friday consecutively.

eg Table

EmpNum, Date, Abstype

001 07/23/2012 VAC *Monday
001 07/24/2012 VAC
001 07/25/2012 VAC
001 07/26/2012 VAC
001 07/27/2012 VAC
003 07/23/2012 VAC * Monday

[code]...

As these are the 2 that run from monday-friday

View 2 Replies View Related

Select Statement With Dates

Feb 6, 2008

right now I am able to retrieve the month and year from a field by month(TS_Date), Year(TS_Date), my question is can I put both results as one so output can say Date 2 2008 instead of two columns with month 2 year 2008.
any help would be appreciated

View 2 Replies View Related

Select All Dates From SQL Datetime Field

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

SELECT From A Specific Range Of Dates?

Aug 29, 2007

Hi all,Right now I have this SELECT statement:SELECT     MUSIC_PK, MUSIC_TITLE, MUSIC_TITLE2, MUSIC_ORIGINAL_SINGER Now I want to add a second SELECT statement (same as above, but with an additional column) if the record was added within the 10 days (while still select other records, I just need to select an additional column). How would I do that? The additional column that I want to select is called MUSIC_NEW (so I can display if the record is new within 10 days.) I have a column in my table named DATE_ADDED, how do I go to calculate if it's within the 10 days since the day it was added to the current time?Thank you very much,Kenny. 

View 8 Replies View Related

Getdate() To Select Dates Less Than 15 Months

Apr 7, 2008

Hi,
I need help. I want to select dates less than 15 months, as i am new to sql server , can anyone advise me.

I tried this query but it gave me the dates that are greater than 15 months

Selec calendardate from table
where calendardate < getdate()-457

Thanks

View 3 Replies View Related

Transact SQL :: Select Conditional Dates

Oct 15, 2015

TSQL statement, it should select the first date of the month at 11:59 PM as Firstdate and Closedate as 10 days later at 11:59 PM.

View 14 Replies View Related

How To Select All The Dates Of Month Even Though There Are No Records?

Nov 28, 2007



I have one SQL Table with 2 columns as below


Column1: ProductionDate - DateTime - Not NULL
Column2: Quantity - Int - Not NULL

Now There are 2 Records in Table

1-1-2007, 5
1-3-2007, 7


Output of Result should be as below

1-1-2007 5
1-2-2007 0
1-3-2007 7
1-4-2007 0
1-5-2007 0
1-6-2007 0
.
.
.
1-31-2007 0

Means Query should return all the dates of Month with Quantity and if no entry in Table then 0 for Quantity.

How to Do it? Please suggest with Query

View 5 Replies View Related

Problem With Dates In Dataset Select Statement

Mar 14, 2007

I'm using the designer to create/modify strongly typed datasets.  I have one select statement that I'm having considerable trouble with.  The user selects search parameters from a form.  These search parameters are used to filter the data sent to a gridview control.  Three of these parameters are almost enough to make me abandon the dataset in this case.The first two are minimum and maximum age.  I have a birth date field, but not an age field - ages have a habit of changing without user intervention <grin> and can be calculated.  In ASP I'd do something like this:strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ") AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")" I can't figure out how to get datediff working in the designer much less testing against upper and lower age limits.The third parameter is astrological sign.  Again, I calculate it based on the birth date.  I don't have a field for it.  I could, but I would have to modify all the pages that add or edit records to the database to insure all the records that have birth dates also have the right sign.  I'm leaning in that direction, but is it possible to accept a sign as a parameter and calculate, based on the birth date, which records qualify?  I need to get the age issue fixed.  The sign is a nice to have, since I can do it another way if I have to. BTW:  I did decide to abandon the dataset in favor of a SqlDataSource control.  This allowed me to build my select string in the code behind the way I would have in ASP.  This resulted in paging and sorting not working properly.  Sorting would be nice, paging is necessary.  I'm pretty sure going back to the dataset or using a stored procedure would fix the paging problem, and I've yet to work with stored procedures (it's on my list of things to learn).   Any comments or feedback on this would be avidly read and appreciated.Diane 

View 8 Replies View Related

Stuck On Select Query Involving Dates

May 11, 2007

Table 1

ID PID From To Code
1 1 14/02/07 17/02/07 X
2 1 17/02/07 19/02/07 X
3 1. 19/02/07 23/02/07 E
4 1 26/02/07 28/02/07 X
5 1 1/4/07 1/5/07 E
6 2 01/03/07 03/03/07 X
7 2 04/03/07 10/03/07 X
8 2 10/03/07 14/03/07 E

Result

ID PID Date
4 1 26/02/07
7 2 04/03/07

I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next €˜from record€™ should be the same date as the €˜to date€™. If the code is E then the next €˜to€™ date can be anytime after the previous €˜to€™ date.
I want to be able to report on all record where there is a day difference between the previous €˜to€™ date. I.e. ID 4 and 7 €“ the previous records both have an X and there is at least a days difference between the dates.

View 4 Replies View Related

T-SQL (SS2K8) :: Select Past 3 Dates And Determine If 1st And 3rd Are Within Timeframe?

Sep 7, 2014

I have a table with addresses and activity dates. I need to be able to retrieve the past 3 activity dates and see if the first and last occurred within 15 days. If so, I need to flag them.

Using max date gets me the last date but not the previous two. I was trying to use top 3 in desc order and that didnt seem to work either.

View 9 Replies View Related

Select Records Between Dates - Query Works In VS 2005 But It Doesn't In Asp 3

Nov 9, 2006

Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance

View 1 Replies View Related

Reporting Services :: Select Between From And To Dates In Multiple Parameter Report

Oct 26, 2015

I have a report that uses several filters including a from and to date filter, I have  a field named TimeLastMod which is a Timestamp. I am extracting the From and To dates from this field using Select format(TimeLastMod,'MMM') as FromMonth,format(TimeLastMod,'MMM') as ToMonth From Eventlogs syntax.

These I pass on to a parameter and eventually to a tablix filter in an between opeartion. When I run the report, the records returned by the report is not what is expected as it sometimes returns an extra months data e.g., using between Aug and Sept will return october data as well.

View 5 Replies View Related

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

Mar 2, 2006

SQL 2005 Dev

How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?

View 3 Replies View Related

How To Select All Dates Upto Todays Date And Include The First Next Future Date

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

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Comparing Dates With Today Dates

Jun 21, 2005

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date.  the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?

View 1 Replies View Related

Generate Dates Between Two Dates

Nov 8, 2006

Hi,

I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.

I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:

SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)

Any advice will be greatly appreciated!

View 1 Replies View Related

Getting Individual Dates Between 2 Dates

Mar 4, 2008

Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?

So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?

View 13 Replies View Related

Leap Year Dates Causing Error When You Pull Dates For Non Leap Year

Feb 28, 2008



I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error


Here is what I'm trying to generate






InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev

ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65


ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
NULL
NULL



Here is the error that I'm getting:



Code Snippet

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.

(4834 row(s) affected)



Here is my Transact-SQL Syntax (summarized because I Couldn't post it):

SELECT

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'

AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'

THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT Trans1.TR_Amount

FROM dbo.Trans Trans1

WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)

AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)

AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,


--AA Script Here AS AARmRev,

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)

= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT SUM(Trans1.TR_Amount)

FROM dbo.Trans Trans1

WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND

trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))

+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND

trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev



FROM dbo.EntityDef INNER JOIN

dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN

dbo.CurrentDate INNER JOIN

dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate

WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')

ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate


I appreciate all your help

Thanks

View 13 Replies View Related

Sql Dates

Mar 30, 2007

Hello All,
 I am trying to Add certain number of days to a particular date.
and my requirement is that it need to exclude all saturdays and sundays and then give me the resultant date in Sqlserver.
 Please can anyone help me in achieving it.
 thanks
Shiva Kumar

View 2 Replies View Related

Dates

Jan 10, 2002

Hi can anyone help me , or am I on the wrong track.

Is there any easy way to create a stored procedure that inserts into a table the relevant months dates into a table based on the month and year as parameters.
I.e say the parameters passed are 01/2001 hence based on this all of the month of January 2001 dates are inserted into a table in this format : 'Jan 01 2001 12:00:00'

Thanks

View 1 Replies View Related

SQL Dates

Apr 19, 2004

OK, so I can return (& therefore use) the last day of last month using

select (GETDATE()- DATEPART ( dd , GETDATE()))

Which returns 31-03-2004 (dd-mm-yyyy)

Has anyone a good way of determining a way of deriving say a date such as 01-01-2004 (1st Jan)....

I'd like to get some data where my dmd.sdate falls always in the previous quarter.. but would like it to be not hard coded...

Currently I have

SELECT cust.no,
cust.surname,
dmd.csan,
dmd.sdate
FROM pdunity.dbo.cust cust,
pdunity.dbo.dmd dmd
WHERE cust.rowno = dmd.rowno_custdmd_cust
AND(dmd.sdate
BETWEEN '01/01/2004'
AND(GETDATE()- DATEPART ( dd , GETDATE())))
ORDER by dmd.sdate asc

Is there some form of function I can use to return 01/01/2004 automatically ?

Will

View 6 Replies View Related

Dates ...

Nov 21, 2005

why does this sql return the dates in different formats? Is it a default setting on the server or do i just handle it on SQL?

use northwind

select top 20 orderdate from orders

select top 20 left(orderdate,10) from orders


thanks

fatherjack

View 1 Replies View Related

Dates For The UK

Feb 11, 1999

We are deploying a system in the UK in SQL Server 6.5. They want the date to default to dd/mm/yy. Is there a way to set this on a permanent basis? Is there a way to set the server so any time a date is display, including getdate(), the result is dd/mm/yy?

View 2 Replies View Related

Dates Between 2 Other Dates

Aug 16, 2002

Hi All

I have a table which contains apart from other fields, 2 dates. i.e. startdate and enddate.

The startdate is always less than / equal to enddate.

I want to write a stored proc where I will give a date as a parameter and it should give me all the records there my date is between the startdate and the enddate.

Also I want to create a temp table in the procedure and the table gets populated with all the dates that comes between startdate and enddate, both dates inclusive.

I know this might be a tall order, but I am new to writing stored procedure, so I will be obliged if somebody can help me out

Yusuf

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved