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
ADVERTISEMENT
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
Nov 19, 2015
I am trying to get a query that subtracts a month and a day from current date in SQL Server.
Currently I have SELECT DATEADD("MM", -1,GETDATE()) which subtracts one month from the date but I want to subtract a month and a day from date.
View 9 Replies
View Related
Sep 21, 2015
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX)
@sql = ' insert into target
select from table_1_currentdate a
LEFT JOIN Table_2_currentdate-1 b
on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
View 3 Replies
View Related
Apr 25, 2008
Hi,
I'm making some sort of application where people can add their resume.
They also need something to add places where they have worked or currently are working.
I have a form where they can add this and i add this experience using the following stored procedure:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_Insert_Experience]
@ExperienceId uniqueidentifier,
@ExperienceEmployee nvarchar(100),
@ExperienceFrom datetime,
@ExperienceUntil datetime,
@ExperienceQualifications nvarchar(250),
@ExperienceTechnologies nvarchar(250),
@ExperienceTasks nvarchar(250)
as
insert into Experiences
values(@ExperienceId,@ExperienceEmployee,@ExperienceFrom,@ExperienceUntil,@ExperienceQualifications,
@ExperienceTechnologies,@ExperienceTasks);
It must be possible to add the place where they currently are working. Then the ExperienceUntil has to be something like still going on. Then I decided that the user then had to set the current date.
But what I want is the following, I want that the ExperienceUntil keeps updating automatically, like everytime i get that record, it has to have current date.
Is this possible ?
But if the ExperienceUntil isn't the current date , it just had to take the supplied parameter date.
View 7 Replies
View Related
Sep 22, 2006
I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.
View 4 Replies
View Related
Dec 29, 2005
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
View 7 Replies
View Related
Mar 10, 2008
I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).
We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.
What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.
Is there a function I can use that can auto-populate for both insert and updates?
View 4 Replies
View Related
Nov 21, 2007
Hi,
I tried to create parameters in my report. I need one date picker. So i select data type as datetime. in default values i selected as non-queried and in date time functions i selected =Today. But am unable to set current date. Please help me to solve it.
Regards
Nataraj.C
View 2 Replies
View Related
Apr 22, 2007
I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.
So I have a question
1.) How do I get today's date in C# and how should this be passed to SQL server?
View 7 Replies
View Related
Jul 23, 2014
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
View 9 Replies
View Related
Oct 21, 2013
the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:
So for instance looking at a copy of one person's date below I have:
PersonID Last Dept Arrival Date To Last Dept Leaving Date From Last Dept Next Arrival Date to Unscheduled Dept
======== ================= ========================= =========================== =====================================
0106573268Dept 5 2013-03-01 2013-03-03 2013-03-05
0106573268Dept 6 2013-02-27 2013-02-27 2013-03-05
0106573268dept 2 2013-02-26 2013-02-26 2013-03-05
In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.
My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID
[code]....
View 11 Replies
View Related
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
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
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
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
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
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
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
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
Jun 8, 2008
I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77
I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
lack of idea to put the TIME condition.
Plz help me..
View 14 Replies
View Related
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
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
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
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
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
Dec 6, 2005
Is there a system word for the current date in a stored procedure or do I have to pass it in as a parameter ?
ie
BK_DateRequired <= Today
View 2 Replies
View Related
Jun 26, 2007
Hello,
I'm new to SQL server and the SQL language itself and I'm having a bit of problem.
Is it possible to obtain the current date from the system?
Something like 'sysdate' or anything like that.
I want to compare the current date with a date stored in the database but I can't seem to find the wright SQL server keyword, or is there none?
Many thanks
View 6 Replies
View Related
Dec 20, 2007
Does anyone know how I can get the current date inserted to a SQL query to only get results of today?
My SQL is:
SELECT Business.BusinessCODE, Business.Name, Business.BusinessNumber, Business.BusinessStatusCODE, Business.Done, Business.StartDATE, Business.EndDATE, Business.TotalPrice, Business.Contribution, Business.BusinessProcessCODE, Customer.Name1, CaesarUser.FirstName, CaesarUser.Surname, BusinessHistory.UpdateDATE
FROM Movex_SMS_50_Server.dbo.Business Business, Movex_SMS_50_Server.dbo.BusinessHistory BusinessHistory, Movex_SMS_50_Server.dbo.CaesarUser CaesarUser, Movex_SMS_50_Server.dbo.Customer Customer, Movex_SMS_50_Server.dbo.relCaesarUserBusiness relCaesarUserBusiness
WHERE Business.CustomerID = Customer.CustomerID AND relCaesarUserBusiness.BusinessID = Business.BusinessID AND BusinessHistory.BusinessID = Business.BusinessID AND relCaesarUserBusiness.CaesarUserID = CaesarUser.CaesarUserID AND
((BusinessHistory.Description='Créé' OR
BusinessHistory.Description='Created' OR
BusinessHistory.Description='Creado' OR
BusinessHistory.Description='Erstellt' OR
BusinessHistory.Description='Creato')AND
((BusinessHistory.updatedate = TODAYS DATE)) AND
(Customer.EmployeeCountCODE=0xEC6D3BDB8B6FD511AC240006294308D9 OR
Customer.EmployeeCountCODE=0xFC673BDB8B6FD511AC240006294308D9))
ORDER BY CaesarUser.Surname, Customer.Name1
View 10 Replies
View Related
Dec 3, 2007
Hi guys,how do i insert the current date in SQL?
View 2 Replies
View Related
Apr 27, 2004
Hi,
Can I write a code in SQL that return the current date? If so, how?
Thanks!
Will
View 2 Replies
View Related
Jan 8, 2007
Hi. I have a stored procedure that looks for events on the current date. I tried using GETDATE() but i realized that is looking for the current date AND time. how can i use this to find only the current date? thanks a bunch for your help!
View 3 Replies
View Related
Apr 22, 2008
hi,
If today is the 22/4/08
how can I get the day last month,
eg. 22/3/08
?thank you
View 9 Replies
View Related
