Transact SQL :: Max And Min Date

Jun 19, 2015

I have date in my table like this

id           from           to
1        01/01/2013           02/01/2014
1        02/02/2014           12/31/9999

so for same id i want min (from ) and max(to) date  in my select statement.

Example ,  for id 1 
id             from             to
1          01/01/2013       12/31/9999

View 3 Replies


ADVERTISEMENT

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

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

SELECT * ,[Due]
  FROM [Events]
 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? How to cast or convert this so that it will work? 

View 24 Replies View Related

Transact SQL :: Selecting Latest Date But Over Another Date Column Including Nulls?

Nov 26, 2015

CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )

[Code] .....

I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3.  

If the declined date is filled in.  Then I want to get the row, when the Datein column value is greater then the declined date only.

I tried grouping it by max date, but   i got an error message when trying this out.  Against the code  

WHERE MAX(Datein) > Declined

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.  What do I need to do to get both my outputs working? 

View 9 Replies View Related

Transact SQL :: Return Date Which Is 15 Working Days Prior To Given Future Date

Oct 28, 2015

i have written a sql function which returns only number of working days (excludes holidays and Weekends)  between given StartDate  and EndDate.
                 
USE [XXX]
GO
/****** Object:  UserDefinedFunction [dbo].[CalculateNumberOFWorkDays]    Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO

[code]...

I need  a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date.  Example scenario:  If i give date as  12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table  tblMasHolidayList where all the 2015 year holidays dates and info are stored.

View 18 Replies View Related

Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date

Aug 31, 2015

So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.

SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()

Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?

View 2 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Column

Apr 19, 2015

My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.

Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01

[code]...

 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01

View 10 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID

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

Transact SQL :: How To Write A Query To Get Current Date Or End Of Month Date

Sep 29, 2015

how to write a query to get current date or end of month date if we pass year and month as input

Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).

View 3 Replies View Related

Transact SQL :: How To Get Date Where Planned Delivery Date Key Is Less Than Getdate

Aug 13, 2015

How to get the date where planned delivery date key is less than getdate()

I want to make a view where I want put the condition :

select CONVERT(datetime, CAST(planned_delivery_date_key AS CHAR(8)), 101) < CONVERT(datetime,getdate(),101)
from qlk_fact_sales_back_order

View 2 Replies View Related

Transact SQL :: Date Variables That's being Set Around Current Date

Nov 23, 2015

I have a the following date variables that's being set around the current date.  how to adjust it to work around a date variable @Date instead of the current date?   Lets the @Date = 2015-06-30 then the it would adjust the variable below accordingly.  I'm assuming the getdate()) needs to be replaced with @Date but I can't seem to get it to work.

Declare @EndOfLMPriorYear  Date = DateAdd(yyyy,-1,EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0)))
Declare @EndOfPriorMonth Date =  EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-2,0))
DECLARE @EndOfLastMonth Date = EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0))

View 3 Replies View Related

Transact SQL :: Getting Closest Date From A Given Date Column

Sep 28, 2015

Basically, the sample raw data is a result from my last cte process which consolidate all the records. I want to find the closest date in PO ended using Receipt_date column but my problem, for this ESN R9000000000019761824, i wanted to get the latest or the last transaction date.

please see below DDL and sample data.

--this is the result from my last cte process
Create table #sample
(ESN nvarchar(35),ESN2 nvarchar(35), Receipt_Date datetime,PO_ENDED datetime)

insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2014-01-09 04:13:29.000')

[Code] .....

Expected result:

ESN-------------------ESN2-------------Receipt_Date-------------POENDED---------------
--------------------------------------------------------------------------------------
990002036924452-------990002036924452--2015-01-07 17:39:44.660--2015-04-23 20:26:55.000
990002038378525-------990002038378525--2015-01-07 05:29:56.923--2015-05-29 14:50:52.000
R9000000000019761824--354403060637509--2015-01-03 19:23:54.980--2015-03-20 16:43:07.000

[Code] ....

View 8 Replies View Related

Transact SQL :: Outputting Date Difference Between Two Date

Sep 11, 2015

I require outputting the date difference between two date's if it is greater than 7(DateDiff(day, DateAdd(day, t.[Started], Nxt.started), (t.[started])) > 7).I get incorrect syntax on my operator.What is the correct code?

View 6 Replies View Related

Transact SQL :: Date Arithmetic With Date And Datetime

Sep 21, 2015

if I do this:

print @@version
print 'arithmetic with datetime'
go
begin try
declare @datetime datetime = getdate()

[Code] ....

... I get this:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) 
Mar 26 2015 21:18:04 
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

arithmetic with datetime
@datetime = Sep 22 2015  1:39PM
arithmetic with date
Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int

Why the inconsistency? Datetime is lenient in this regard - I can even do set @datetime += 0.5 (although the meaning is less intuitive).

View 6 Replies View Related

Transact SQL :: Expand Date Within Date Parameter

Jul 23, 2015

I am working into a t-sql where i need to expand the date within a date parameter. For example:

Start Date = '1/1/2014'
End Date = '12/31/2014'

I want to expand this as:

01/2014
02/2014
03/2014
04/2014
05/2014
06/2014
up to 
12/2014

View 4 Replies View Related

Transact SQL :: How To Get Date - Last Monday From Given Date

Jun 15, 2015

How can I get a date that would be last/previous Monday from given date?

For example for given 22/01/2015 it would give 19/01/2015.

View 16 Replies View Related

Transact SQL :: Using Different Date Criteria

Nov 16, 2015

I have different codes and I want to specify different date of birth range based on selected code in the parameters.

DECLARE @CODE VARCHAR(5) = 'FXE'
SELECT * FROM TABLE
WHERE CODE = 'FXE' and
AND DATE_OF_BIRTH
BETWEENDATEADD(month,-15,GETDATE())
AND DATEADD(year,-1,dateadd(YEAR,-0,cast(GETDATE()asdate))))

How I can do for one code but I cant get my way around bringing another code 'WHY' which has a different date of birth range. Basically I want a user to be able to change a code in the parameter and display results based on  the parameter selected.

View 9 Replies View Related

Transact SQL :: Get Max Date For Data Available For All IDs

Jul 19, 2015

I have a temp table

drop table #temp
create table #temp (id int, dat datetime)
insert into #temp values(1,'2015-03-01')
insert into #temp values (2,'2015-02-01')
insert into #temp values (2,'2015-01-01')
insert into #temp values (3,'2015-01-01')
insert into #temp values (3,'2015-03-01')
insert into #temp values (3,'2014-12-01')
insert into #temp values (2,'2014-12-01')
insert into #temp values (1,'2014-12-01')
select * from #temp

The result i am expecting is  2014-12-01. I need to get the max(dat) for data available for all ids.In the sample set, only for 2014-12-01, there is a row for id 1,2,3. I need to get that date as result. 

View 11 Replies View Related

Transact SQL :: Get Data For First And Last Date

Apr 23, 2015

I have a table that capture the size of databases every week.

CREATE TABLE [dbo].[DBGrowthRate](
 [DBGrowthID] [int] IDENTITY(1,1) NOT NULL,
 [DBName] [varchar](100) NULL,
 [DBID] [int] NULL,
 [CurSize] [decimal](10, 2) NULL,
 [Gatherdate] [datetime] NULL,
 [Serversname] [varchar](50) NULL
) ON [PRIMARY]

What I want is to make a query that will return

DBName, Cursize (oldest Gatherdate, CurSize (most recent GatherDate, ServersName

How to make this happen, I have tried working with Max and Min Gatherdate but that doesn't work.

View 7 Replies View Related

Transact SQL :: Query To Get MAX Date

Jun 23, 2015

I need query to get the Max date of CODE.

OID   CODE   Current_date
3710 250    01/01/1997
3910 250    03/03/1998
4100 250    01/01/2014
1200 251    01/01/2013
1301 252    01/01/2001
1450 252    01/01/2014
1451 252    01/01/2015

Expected result after taking max(effdate) of CODE

OID   CODE   Current_date
4100 250    01/01/2014
1200 251    01/01/2013
1451 252    01/01/2015

View 7 Replies View Related

Transact SQL :: Date With HH:MM Appended?

Jul 7, 2015

How do i get the mentioned format. if today is 7th July then,2015-07-07 15:30 i.e. date with HH and MM appended in today's date?

want just one simple statement. don't want to write big query as i want to use this condition in one of the joining criteria.

View 6 Replies View Related

Transact SQL :: Get One Record By ID Which Has Max Date

May 4, 2015

I have records like this

id      address1          date
1      xyz                 01/01/2013
1       abd                01/01/2014
2       dfg                 01/03/2015

Now what I want is just 1 record by id which has max date,if there is 2 records for same id, if there is 1 record then it should come.

So here result should be

1       abd                01/01/2014
2       dfg                 01/03/2015

View 12 Replies View Related

Transact SQL :: Date Format Conversion

Jul 24, 2015

Hoe to convert date format

My table contains date as " 13FEB2015:08:54:45 " need to be change in datetime or date.

View 10 Replies View Related

Transact SQL :: Convert Varchar To Date?

Nov 27, 2015

Having an issue getting this done. The datatype in the table is varchar(50). The values in the table are

11122015
11122015
11122015

Nothing has worked so far.

View 3 Replies View Related

Transact SQL :: Date Between 1st And 10th Of Every Month

Nov 12, 2015

I have the following requirement I need to extract all records for min or max date between the 1 and 10th of every month, the following query works but I find some dates that are not in the range specified.

Select
[EventType],ItemName,FORMAT([TimeLastModified],'MM')asFromMonthValue,
FORMAT([TimeLastModified],'MM')asToMonthValue,
FORMAT([TimeLastModified],'MMM')asFromMonthLabel,
FORMAT([TimeLastModified],'MMM')asToMonthLabel,  
min([TimeLastModified])asMinDate,max([TimeLastModified])asMaxDate,ModifiedBy,Casewhen[EventLog].EventType=1
Then 
'Item Added'

[Code] .....

View 5 Replies View Related

Transact SQL :: Change A Date WHERE Statement

Jul 22, 2015

I would like to change

WHERE dbo.IP_ADMISSION.DischDate >= GETDATE()-7 AND dbo.IP_ADMISSION.DischDate < GETDATE()

to capture Monday to Sunday data only irrespective of what day I run the report

View 16 Replies View Related

Transact SQL :: How To Extract Date Value From XML Field Value

Oct 5, 2015

I am having an XML field in a table "RMRECC" (field name: "Amount") and the value of the field is given below.

<X C="0" I="1">
<E D="1Y0M0W0D" P="0" A="0.05" />
<E D="1Y0M0W0D" P="0" A="0" />
<E D="1Y0M0W0D" P="0" A="0.03" />
</X>

I need to extract the date value "1Y0M0W0D" in MM/DD/YYYY format.how this can be handled in SQL query?

View 2 Replies View Related

Transact SQL :: Sum Amount By Grouping Date

Oct 3, 2015

I have a table that have customer name and their bill amount

a) tblBilling

which shows records like below (Billing Dates are shown in Year-Month-Day format)

Invoice   Customer             BillingDate       Amount            Tax
--------------------------------------------------------------------------
1             ABC                       20015-10-2           1000.00            500.00
2             DEF                        20015-10-2           2000.00        1000.00
3             GHI                        20015-10-2           1000.00            500.00
4             JKL                         20015-10-3           5000.00          2500.00
5             MNO                     20015-10-3           1500.00            750.00
6             PQR                       20015-10-4            500.00            250.00
7             STU                        20015-10-4           1000.00           500.00
8             VWX                      20015-10-4            2500.00         1250.00

I want to perform a query that should SUM Amount and Tax Colums by date basis, so we could get the following result

 BillingDate       Amount            Tax
--------------------------------------------------------------------------
20015-10-2           4000.00            2000.00
20015-10-3           6500.00            3250.00
20015-10-4           4000.00            2000.00

View 3 Replies View Related

Transact SQL :: How To Get Max Date And All Rows Data

Oct 17, 2015

i have this table the TDate in string (not date)

MEN
===
id       |      Name    |      TDate
1        |   aa           |       01.05.2015
2        |   bb           |       05.05.2015

View 6 Replies View Related

Transact SQL :: Cast Zero AS DATETIME2 Or DATE

Jul 22, 2015

Casting 0 to SMALLDATETIME or DATETIME is allowed in T-SQL which produces to 1900-01-01.  However, you can't cast 0 to DATE or DATETIME2.  Why is that?

View 15 Replies View Related

Transact SQL :: How To Sort Date By Month

Jun 9, 2015

I have a filed named 'date'. I convert this into 101 format. Now I want to sort that date by year not by month.

View 12 Replies View Related

Transact SQL :: Display Records According To Date

Oct 2, 2015

I have a table that have student names and their birth dates just like below

a) tblStudentInfo

which shows records like below (Birth Dates are shown in Year-Month-Day format)

ID   StudentName        BirthDate   
1     ABC                       2002-12-25 
2     DEF                        2002-09-10  
3     GHI                        2002-09-19 
4     JKL                        2002-06-10 

[code]...

I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below

ID   StudentName        BirthDate   
1     ABC                       2002-12-25 
2     VWX                      2002-01-01
3     STU                        2002-02-03 
4     PQR                     2002-03-05 

[code]...

View 3 Replies View Related

Transact SQL :: Get Date Of All Weekends Of The Year Without CTE

Sep 1, 2015

I want go get all weekends of the year (year dynamic) with out CTE concept, because I need to implement in 2005 version.I have googled but getting only CTE examples.

Query to get the weekends in a year.

View 3 Replies View Related







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