Transact SQL :: List Date Between Dates

Aug 22, 2015

i have a table like below
CREATE TABLE #Test (FromDate DATE,ToDate DATE)
insert into #Test VALUES ('2015-08-08','2015-08-11')
insert into #Test VALUES ('2015-08-13','2015-08-16')
insert into #Test VALUES ('2015-08-19','2015-08-21')
SELECT * from #Test
drop TABLE #Test

i need to display the dates as single column between from and todate.my expected result is like below

CREATE TABLE #Result (ResDate DATE)
insert into #Result VALUES ('2015-08-08')
insert into #Result VALUES ('2015-08-09')
insert into #Result VALUES ('2015-08-10')
insert into #Result VALUES ('2015-08-11')

[code]....

View 2 Replies


ADVERTISEMENT

Transact SQL :: How To Generate Date Ranges From Given List Of Dates

Sep 10, 2015

I want generating Valid date ranges from any list of dates.

The List of Dates could be generated from the below TSQL - 

SELECT '2015-06-02' [Date] UNION ALL
SELECT '2015-06-13' UNION ALL
SELECT '2015-06-14' UNION ALL
SELECT '2015-06-15' UNION ALL
SELECT '2015-06-16' UNION ALL
SELECT '2015-06-22' UNION ALL
SELECT '2015-06-23' UNION ALL
SELECT '2015-06-24'

And the expected output should look like - 

SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL
SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL
SELECT '2015-06-22' FromDate, '2015-06-24' ToDate

View 2 Replies View Related

How Do Order The List By Date To Show For New Dates.

Aug 2, 2007

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWHCConnectionString %>"
SelectCommand="SELECT [Title], [URL], [Date] FROM [Article] ORDER BY [Date] DESC"></asp:SqlDataSource>


<asp:Repeater id="myRepeaterUL" runat="server" DataSourceID="SqlDataSource1">
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li><a href="<%# DataBinder.Eval(Container.DataItem, "URL") %>"><%#DataBinder.Eval(Container.DataItem, "Title")%></a><br /><%# Eval("Date") %></li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>
 This is my code above, I am trying to order them to show the four new list of news. Here is a picture, yeah its old and everybody loves pictures. see the box on the right, i want to show only four, not all of it.  

View 2 Replies View Related

Transact SQL :: Check If A Date Is Within A Range Of Dates?

Aug 20, 2015

Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.

All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.

My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?

View 14 Replies View Related

Transact SQL :: Date Difference Between Two Dates In Due And OverDue

Sep 25, 2015

I want a difference in days 

Select datediff(dd,Target_Date,Achv_Date) 
Now , checks are 
1] when target date greater than achv_Date the difference should be greater than 0 
means for FileID 77608 
Select datediff(dd,'2015-09-24 00:00:00.000','2015-09-24 10:42:32.823')
 
i am getting -6 it should be 6 cant switch Target_Date and Achv_Date in datediff else i will get opposite result in first four records basically, i want a two column TAT and Status beside  achv_date based on the values of two dates difference see above ..and also want a result of (No. of Yes in status / No. of Files that has achv_date )i.e. result= (7/8) = 87% 

View 6 Replies View Related

Transact SQL :: Combining Parts Of 2 Dates Into Third Date

May 7, 2015

In a stored procedure I have 2 dates that I need to combine parts of into a third date. I need the year from date1 and the month/day from date2.

Date1 = '2015/6/1'
Date2 = '2016/1/1'
Date3 needs to be '2015/1/1'

I have been messing around with datepart and dateadd with no luck.

View 3 Replies View Related

Transact SQL :: How To Get Four Quarter End Dates Based On Given Date

Jun 3, 2015

I have a simple following table which is having only one date column.

CREATE TABLE TEST_DATE
(
InputDate DATE
)
GO
INSERT INTO TEST_DATE VALUES('01-01-2015')
INSERT INTO TEST_DATE VALUES('06-25-2015')
INSERT INTO TEST_DATE VALUES('11-23-2014')
GO
SELECT * FROM TEST_DATE;

And the expected out put would be as follows:

I want to derive a Four Quarter End Date based on Date selected.

For Example if i select 01-01-2015 then
First Quarter End Date would be Previous Quarter End Date
Second Quarter End Date would be Current Quarter End Date
Third Quarter End Date would be Next Quarter End Date
Fourth Quarter End Date would be Next +1 Quarter End Date Like that

View 9 Replies View Related

Transact SQL :: Query To Get Date Span Between Dates

May 23, 2015

I need to calculate the amount of time between each visit. I am pulling the Row Number for my visits and now I need the date span that goes between each day. I also need a new column that returns a Yes or a No if the date span exceeds 3 years.

SELECT
ROW_NUMBER ( ) OVER ( PARTITION BY pv.PatientProfileId ORDER BY pv.Visit ASC ) AS RN
, CONVERT ( VARCHAR ( 20 ) , pv.Visit , 101 ) AS Visit
, pv.TicketNumber
, vstatus.Description AS VisitStatus
, doc.ListName AS Doctor

[Code] ....

View 3 Replies View Related

Transact SQL :: Combining Dates To Group Into A Date Range?

Nov 17, 2015

My scenario is: a person has many events, all based on a date.  I need to aggregate the person to show min and max dates for a period, the period being defined as ending when there is not an event following the next date.

DECLARE @Events TABLE
(
PK_EventINTIDENTITY(1,1) PRIMARY KEY
,FK_UserINTNOT NULL
,EventDateDATENOT NULL
)
DECLARE @User TABLE

[Code] ....

I would expect the groups to look something like below:

Is this where a recursive CTE may be used?

View 6 Replies View Related

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

Oct 14, 2015

Here I have 2 Dates. CreatedDttm & ModifiedDttm.

I want  - DATEDIFF(Day,CreatedDttm,ModifiedDttm)  and I have to exclude the Weekend days from that query result.

View 10 Replies View Related

Transact SQL :: Create Comma Separated List For Each Account And Date?

Jun 19, 2015

i have the following:

DECLARE @Table TABLE (
 OrgRoleNumTxt VARCHAR(10)
, AccountNm varchar(100)
, EffectDate DATETIME
, OperationNm varchar(100)
, Premium decimal(18,2)
)
Insert into @Table(OrgRoleNumTxt, AccountNm, EffectDate, OperationNm, Premium) VALUES
('00236', 'R.R. Donnelley', '2010-01-01', 'Chicago', 1000),
('00236', 'R.R. Donnelley', '2010-01-01', 'Boston', 3000)
select *
from @Table

but want this

Is it possible using basic T-SQL?

View 8 Replies View Related

Get List Of Dates Between A Given Range.

Oct 12, 2006

I'm trying to get a list of dates (actually, only the mondays) between a given range of two dates.Any help on the SQL statements I need to use, I'm having no luck.Thanks in advance.

View 3 Replies View Related

GENERATING A LIST OF DATES

Jan 25, 2002

Can someone show me how to load a list of dates into a table.
(i.e. from Jan. 1, 1995 to Jan, 1, 2005)

Thanks in advance!
BV

View 1 Replies View Related

List Parameterized Dates

Apr 4, 2007

Hello,



For my report I want to have the days listed as a dropdown with simple dates during the month (3/27/2007), but there may be many records for that date as it is using the time as well. Unfortunately I don't even know where to begin



Ultimately the user will be given a dropdown listbox showing the simple dates only for the current month and on selection user will view only that days report. How do I basically treat each day as a group in the parameter?



Thanks!

View 7 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

SQL Server 2012 :: List Dates In Columns

Nov 11, 2014

I have a table (Event_Table) like:

EmployeeID, CustomerID, Date
1, 11, 2014-11-11
2, 13, 2014-12-10
1, 11, 2014-12-21
2, 13, 2015-01-11
1, 11, 2015-03-02

And now I would like to have a summary with a unique Employee/Customer combination and 3 Date columns like:

EmployeeID, CustomerID, Date1, Date2, Date3
1, 11, 2014-11-11, 2014-12-21, 2015-03-02
2, 13, 2014-12-10, 2015-01-11

Dates should be arranged with the first date in Date1, the next in Date2 and the third in Date3 (if there are forth and more dates I don´t care)

View 2 Replies View Related

Dynamic CASE Statement Based On List Of Dates

Oct 5, 2007

I have the following table of data.  I need to take a date from a large table and do the following case:CASEWhen date < date(0)     Then '0'When date between date(0) and date(1)      Then '1'When date between date(1) and date(2)     Then '2'When date >= date(3)      Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense.  Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date.  Any ideas how to manipulate these dates into a case statement?  Don't worry about the second table below.  I just wanted you to see why I need to return an int from the Case function.thanksMilton



Dates Table

Date

4/1/2003

1/1/2006

4/2/2007

Fee Table



Date
Period
Class
Fee

1
Daily
True
329

1
Half Day
True
178

1
OT
True
49

1
Hourly
True
41

1
Daily
False
156

1
Half Day
False
86

1
OT
False
27

1
Hourly
False
19

2
Daily
True
355

2
Half Day
True
192

2
OT
True
50

2
Hourly
True
44

2
Daily
False
171

2
Half Day
False
92

2
OT
False
28

2
Hourly
False
21

3
Daily
True
364

3
Half Day
True
197

3
OT
True
51

3
Hourly
True
45

3
Daily
False
175

3
Half Day
False
94

3
OT
False
29

3
Hourly
False
21

View 3 Replies View Related

Transact SQL :: How To Sum All Dates

May 29, 2015

I want to sum all the dates and show the total date sum. How can I do that in SQL 

For these dates
00:35
00.40
00.55
03.50

View 5 Replies View Related

Transact SQL :: Comparing Two Dates

Aug 4, 2015

I have a date comparison situation in which I will have a column with a date and will have another value containing a GETDATE() minus two weeks. I want to be able to compare both dates and get the MIN date between the two. I'm not sure how to use the MIN(Date) in this scenario since the comparison won't be between two different columns, but between one column and a random date generated by the GETDATE() minus two weeks.

View 6 Replies View Related

Transact SQL :: Get Expiry Dates From CC

Sep 18, 2015

I have a Column CCExp Varchar(7)  that stores data like below

NULL
'01/15'
'02/15'
'03/15'
'04/15'
'05/15'

[Code] ...

I am trying to get all expired cards which means, I am trying to get all cards which are behind sept 2015..like when I query I would like to get 8/15 and so on expired cards I am able to get the year , but with the below query I am getting all months less then 9 but i am missing 10,11,12 months cards values also I am having Conversion failed when converting the varchar value '#N' to data type int.

SELECT  CustomerBillingInfoID ,
        CCExp
FROM    CustomerBillingInfo
WHERE   LEFT( CCExp, 2) < MONTH(GETDATE())
        AND RIGHT( CCExp, 2) <= RIGHT(YEAR(GETDATE()), 2)
and CCExp NOT LIKE  '#%'
and CCExp not like 'NULL'

View 8 Replies View Related

Transact SQL :: How To Compare More Than Two Dates In Server

Oct 10, 2015

I have three date columns startdate,enddate,rmd

I need to write a query like get all the rows by comparing rmd with system todaydate and these must be with in startdate and enddate

id startdate enddate rmd
1  10-oct-15   16-oct-15    11-oct-15
2   11-oct-15    14-oct-15     11-oct-15
3   09-oct-15     11-oct-15      10-oct-15
4   07-oct-15      08-ot-15        07-oct-15
5
if sysdate(today) is 11-oct-15 then i need to get all the rows of 1, 2,3

View 5 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

Transact SQL :: Repeatedly Assign Set Of Dates To Each ID?

Nov 18, 2015

I have a table with 3 columns , let say (PatientID  int, AppointmentDate date, PatientName varchar(30))

My source data looks in below way..

PatientID        AppointmentDate      PatientName
  1                 01/01/2012          Tom
  2                 01/10/2012          Sam
  3                 02/15/2012          John

I need output in below way..

PatientID        AppointmentDate      PatientName
  1                 01/01/2012          Tom    (actual patient record)
  null              01/10/2012          Tom
  null              02/15/2012          Tom
  null              01/01/2012          Sam
  2                 01/10/2012          Sam     (actual patient record)
  null              02/15/2012          Sam
    null              01/01/2012          John
  null              01/10/2012          John
  3                 02/15/2012          John     (actual patient record)

I need t-sql to get above output. Basically the appointment dates are repeatedly assigned to each patient but only difference is patientid will be not null for actual patient record.
 
Create table sample (PatientID  int null, AppointmentDate date null, PatientName varchar(30) null)

Insert sample  values (1,'01/01/2012' ,'Tom'),
(2,'01/10/2012','Sam'),
(3,'02/15/2012','John')

View 2 Replies View Related

Transact SQL :: Looping Through Dates In Server

Jun 21, 2015

I have a sql code like this:

declare @currentDate datetime,@enddate datetime;
select @currentDate = '06/1/2015';
select @enddate='06/05/2015'
while @currentDate <= @enddate
begin

-- do whatever is needed

select @currentDate = dateadd(DAY,1,@currentDate);
select @currentDate as currentdate
end

In result am not getting the current date. am getting date from 06/02/2015.i want to get records from 06/1/2015 to 06/05/2015. total 5 records..but now i am getting only 4 records.

How I can get full records.

View 18 Replies View Related

Transact SQL :: Displaying POs With Due Dates Greater Than Three Days

Apr 24, 2015

What I want to see is how to show PO's who's due dates  are > three

Select * from mytable
where myorderstatus = 'onorder'

This is my duedate format and what I want is any that past that date over 3 days

2014-08-11 00:00:00.000

View 11 Replies View Related

Transact SQL :: Trying To Pass Dates To Stored Procedure

Oct 13, 2015

We've researched this, and some people are stating convert to nvarchar first. I'd like to keep the date as a date.Here is how I am trying to call it:

Declare @FromDate as date
Declare @ToDate as date
Set @FromDate=Convert(date,'09-01-2015',110)
Set @ToDate= Convert(Date,'10-13-2015',110)
Select @FromDate

[code]...

How can I keep the date as a date and still pass it to stored procedure.

View 3 Replies View Related

Transact SQL :: Aggregate 2 Dates And Concatenate Into New String

May 21, 2015

Have this table

ACCOU  NAME      NAME TODATE                            ID     EDUCAT    EXPIRYDATE
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL  2018-02-11 00:00:00.000

Need to get it like

ACCOU  NAME      NAME TODATE                            ID     EDUCAT    EXPIRYDATEstring
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL  2018-02-11 00:00:00.000

In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.

View 11 Replies View Related

Transact SQL :: Overlapping Dates For Same Member Query

Oct 18, 2015

IF OBJECT_ID('tempdb..#OverLappingDates') IS NOT NULL
    DROP TABLE #OverLappingDates
    CREATE TABLE #OverLappingDates
(
    MemberID           Varchar (50),
    ClaimID               Varchar(50),
    StartDate             DateTime,
    EndDate               DateTime
)

[Code] ....

I need to select only records where dates are over lapping for the same memberid...For this scenario i need an output First 2 records  (MemberID 1 has 2 claiims overlapping dates).                

View 2 Replies View Related

Transact SQL :: Pull Dates From Database And Then Compare

Jun 10, 2015

I need to pull dates from a DB2 database via TSQL (Linked server - IBM DB2 for i IBMDASQL OLE DB Provider) and compare it to today for a less than or greater than type comparison.

Database: DB2, Customer information housed here
Columns:
UTOFMM - Month (2 character, numeric)
UTOFDD - Day (2 character, numeric)
UTOFYY - Year (2 character, numeric. Problem: years from 2000 to 2009 are stored as 0, 1, 2, ... etc)
UTOFCV - Century Value (2 char, numeric.  0 = before 2000, 1 = in or after 2000)

I need to concatenate the date to be "sql" friendly, and then compare to today's date.  It's to find any customer with date values in the fields above, and then differentiate between dates before today and after today.Here is the snippet of what I'm trying to fix.  This portion of a nightly job is just checking for <u>any</u> value in the UTOFMM column of the current record.

Add Customer ID
Update [responder].[Temp_RX_CUSTOMERS]
set CustomerID = lf.UTCSID
from [responder].[Temp_RX_CUSTOMERS] LEFT Outer Join
[HTEDTA].[THOR].[HTEDTA].UT210AP lf ON [responder].[Temp_RX_CUSTOMERS].LocationID = lf.UTLCID
where lf.UTOFMM = 0
GO

View 4 Replies View Related

Transact SQL :: Check Constraint With UDF To Prevent Overlapping Dates?

Oct 6, 2015

I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table.  The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?

CREATE TABLE t1 (d1 date,d2 date)
go
create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT
AS
BEGIN
 IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2)
  RETURN 1
  RETURN 0
END
go
alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0)
go
insert into t1 values ('2015-01-01','2015-02-01')
insert into t1 values ('2015-01-15','2015-02-15')

--BOTH inserts result in the following message:  The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".

drop table t1
drop function dbo.dt_overlap

View 14 Replies View Related

Transact SQL :: BETWEEN Using String Dates Doesn't Work In Some Cases

Sep 21, 2015

I put this series of select statements to verify that the BETWEEN statement is working properly. I should always get “Between” below.

SELECT
CASEWHEN'1/1/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END
SELECT
CASEWHEN'1/31/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END

[Code] .....

View 4 Replies View Related

Transact SQL :: Total Number Of Days Between Two Dates By Year?

May 6, 2015

I am currently working on a T-Sql query(Sql server 2008) to calculate total no of days between date ranges by year

Table:

Start Date End Date
01/01/2013 04/30/2014
11/01/2014 05/31/2015
06/01/2015 12/31/2015

My expected result.

2013 - 365
2014 - 181
2015 - 365

Note:

Date range can span b/w  multiple years

Date ranges will not overlap

I just want the total number of days covered by the range for each year.

Is there any simple way to do this calculation.

View 9 Replies View Related

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 Replies View Related







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