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
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?
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.
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.
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?
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
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).
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))
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')
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?
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).
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.
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.
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'
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
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.