Transact SQL :: How To Get Date - Last Monday From Given Date
Jun 15, 2015How 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.
ADVERTISEMENT
Last Monday Date As Default Parameter Value
Dec 3, 2007
I have a stored procedure that grabs the Region key from the user, and the date. But i want to set up 5 reports, for day of the week. So i want to make the second parameter a defualt parameter set by me in each report. which is better then having to create 5 stored procedures with the same info just different dates.
when i try to enter the same data i have in my stored procedure to get Mondays date, i get an error.
Here is what i entered in the report parameter default expression box for the date :
=DATEADD(dd, - 6, DATEADD(dd, 1 - DATEPART(w, GETDATE()), CONVERT(varchar, GETDATE(),
101))))
but i keep getting an error that says:
The Value expression for the report parameter €˜Daily_activity_statistics_datetime€™ contains an error: [BC30451] Name 'dd' is not declared.
Build complete -- 1 errors, 0 warnings
whats wrong with it? This works fine in the stored procedure for the where clause. Please help! this would save me alot of time.
T-SQL (SS2K8) :: Set Shipment Date To Monday Instead Of Thursday?
Oct 27, 2014In my below T-sql the shipment date is set to next Thursday, if the shipment is missed in current week. Now, I've to change the t-sql code to change the shipment dates to Monday instead of Thursday.
USE tempdb;
GO
DECLARE @Date DATE;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
[code]...
Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)
Apr 30, 2015So 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 "-"
Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String
Nov 16, 2015SELECT * ,[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?Â
Transact SQL :: Selecting Latest Date But Over Another Date Column Including Nulls?
Nov 26, 2015CREATE 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?Â
Transact SQL :: Return Date Which Is 15 Working Days Prior To Given Future Date
Oct 28, 2015i 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.
Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date
Aug 31, 2015So 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?
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
Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID
Apr 23, 2015My 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
Transact SQL :: How To Write A Query To Get Current Date Or End Of Month Date
Sep 29, 2015how 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).
Transact SQL :: How To Get Date Where Planned Delivery Date Key Is Less Than Getdate
Aug 13, 2015How 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
Transact SQL :: Date Variables That's being Set Around Current Date
Nov 23, 2015I 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))
Transact SQL :: Getting Closest Date From A Given Date Column
Sep 28, 2015Basically, 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] ....
Transact SQL :: Outputting Date Difference Between Two Date
Sep 11, 2015I 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 RelatedTransact SQL :: Date Arithmetic With Date And Datetime
Sep 21, 2015if 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).
Transact SQL :: Expand Date Within Date Parameter
Jul 23, 2015I 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
How To Use Convert Date Statement In CmdInsert.Parameters.Add(Date,SqlDbType.DateTime).Value = Date
Sep 21, 2006HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies View RelatedTransact SQL :: How To Generate Week Ranges From Monday To Sunday
May 28, 2015I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.Â
Note : Week should be generated from Monday to Sunday within desired date range
How To Convert A Date (date/time) To A Julian Date
Jun 13, 2002In SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View
Jul 29, 2015My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
Transact SQL :: Max And Min Date
Jun 19, 2015I 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
Transact SQL :: Using Different Date Criteria
Nov 16, 2015I 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.
Transact SQL :: Get Max Date For Data Available For All IDs
Jul 19, 2015I 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.Â
Transact SQL :: Get Data For First And Last Date
Apr 23, 2015I 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.
Transact SQL :: Query To Get MAX Date
Jun 23, 2015I 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
Transact SQL :: Date With HH:MM Appended?
Jul 7, 2015How 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.
Transact SQL :: Get One Record By ID Which Has Max Date
May 4, 2015I 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
Transact SQL :: Date Format Conversion
Jul 24, 2015Hoe to convert date format
My table contains date as " 13FEB2015:08:54:45 " need to be change in datetime or date.
Transact SQL :: Convert Varchar To Date?
Nov 27, 2015Having 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.
Transact SQL :: Date Between 1st And 10th Of Every Month
Nov 12, 2015I 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] .....
Transact SQL :: Change A Date WHERE Statement
Jul 22, 2015I 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
Transact SQL :: How To Extract Date Value From XML Field Value
Oct 5, 2015I 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?