Trouble With Previous Date Code
Oct 24, 2007
I am new to SQL; the following code is supposed find comments that are not null on one day before the studydate.
SELECTp.PatientID,P.LastName,P.FirstName,P.BirthTime,s.StudyDate,s.Modality,s.Comments
FROMStudies s INNER JOIN Patients p
ON s.PatientID = p.PatientID
WHEREs.Comments IS NOT NULL
AND s.StudyDate >= CONVERT(char,DATEADD(day, -12, GETDATE()),101)
AND s.StudyDate < CONVERT(char,DATEADD(day, -11, GETDATE()),101)
Is there a problem with this code? I expect there is because when I ran the query it didn't return any results even though I am positive there are comments in the database one day before the studydates.
Any help would be greatly appreciated
Thanks a ton,
Regards
-avery
View 2 Replies
ADVERTISEMENT
Sep 10, 2013
Need getting a query which I will get previous year, previous month first day everytime i run the query.
Ex: If i run the script on 9/10/2013 then result should be 8/1/2012. (MM/DD/YYYY)
View 4 Replies
View Related
Jul 23, 2005
Hi there,See if you can help me with the following:I need to write an SQL code that will return me:The 1st day & the Last day of the Previous Month in the following format(smalldatetime):yyyy-mm-dd hh:mi:ss (24h)Regards,--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200507/1
View 5 Replies
View Related
Mar 14, 2014
Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2.
View 9 Replies
View Related
Aug 15, 2006
I need start and end date of previous month, such as StartDate=07/01/2006 and EndDate=07/31/2006 for currentdate 08/156/2006
How can I do this?
View 1 Replies
View Related
Aug 15, 2006
I need start and end date of previous month, such as StartDate=07/01/2006 and EndDate=07/31/2006 for currentdate 08/156/2006
How can I do this?
View 6 Replies
View Related
May 15, 2008
I have an sub-period end-date column, in a row set of (let's say) 2 records.
How Can I grab a previous record and use it's SUB_PD_END_DT
to add a day and get the current record's sub period beginning date?
i.e.
row 1 = col1, col2, SUB_PD_END_DT = (2008-05-02)
row 2 = col1, col2, SUB_PD_END_DT = (2008-05-09)
therefore, row 2 = SUB_PD_start_DT = (2008-05-03)
Any help would be great.
Thanks
JDA
View 9 Replies
View Related
Sep 17, 2007
How do i get only the records for everything with only the dates from last week (monday-friday)? I want to have this run every week for the previous week. Here's my stored procudure. Right now i have this to run for the day before, but now i need it for the week before. So its a range. Please Help. Thanks!
Code Snippet
CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]
(@Classification varchar(50))
AS
BEGIN
SELECT RC_STAT.dbo.Brand_Dimension.Report_Level,
RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],
RC_STAT.dbo.Brand_Dimension.Item_Merged,
Qry_Sales_Group.Region_Key,
Qry_Sales_Group.Region,
Qry_Out_Of_Stock.product_structure_level,
Qry_Out_Of_Stock.product_entity_code,
Qry_Out_Of_Stock.cycle_day,
Qry_Out_Of_Stock.customer_code,
Qry_Out_Of_Stock.description,
Qry_Sales_Group.Code,
Qry_Sales_Group.SR_Name,
Qry_Sales_Group.Name AS Territory_Name,
Qry_Out_Of_Stock.Store_Name,
Qry_Out_Of_Stock.time_log,
Qry_Out_Of_Stock.out_of_stock,
Period_Code
FROM RC_STAT.dbo.Brand_Dimension INNER JOIN
dbo.Qry_Out_Of_Stock ON
RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN
dbo.Qry_Sales_Group ON
dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
Where Classification=@Classification DateDiff(dd,0,dbo.Qry_Out_Of_Stock.time_log) =
case
when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3
else datediff(dd,0,GetDate()) - 1
end
END
SET NOCOUNT OFF
View 5 Replies
View Related
Sep 22, 2015
I have one table :
file _target
which has below records..
file_target_ID is identity column which will repeat per files_ID
Now, i just shown Target log for file_ID 77796 see the last Target Date i want another column which returns a previous log Target date for each files beside Target date column
Like this ..
Target Date New Column
2015-09-09 00:00:00.000 2015-09-16 00:00:00.000
2015-09-16 00:00:00.000 2015-09-25 00:00:00.000
2015-09-25 00:00:00.000 New Target date after 25-9-2015
how to do this in SQL Server ??
using Cross Apply ?? Row_Number() ??
View 3 Replies
View Related
Aug 22, 2014
How do I get first day of last month of previous quarter from today's date? I know my question is little confusing. I need to get 06/01/2014 using t-sql.
View 2 Replies
View Related
Nov 4, 2015
I want to find previous date from selected date. below is the sample data.
DECLARE @StartDate SMALLDATETIME = '1/11/2016'
declare @tempdat table(repdate smalldatetime)
insert into @tempdat values ('10/26/2015')
insert into @tempdat values ('10/29/2015')
insert into @tempdat values ('11/1/2015')
insert into @tempdat values ('11/27/2015')
insert into @tempdat values ('11/25/2015')
insert into @tempdat values ('11/20/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/11/2015')
insert into @tempdat values ('11/11/2015')
Now if i pass the date '10/26/2015' then i want select prev date of passed date. in this example no prev date is available, so result set would be nothing.
if i pass the date '11/10/2015' then result should be '11/1/2015' which is prev small date available in table.
View 4 Replies
View Related
Jan 18, 2014
I have a table where the Primary Key is of datatype DATE. This column is also a clustered index. Since not every day is in this column i am looking for a way to address a date one ( or two, or three) Dates before a given date. Something like:
select * from myTable
where [date] = one record previous to record where date = '22-05-2000'
How is that possible.
View 1 Replies
View Related
Oct 27, 2015
I've got a cube with :
- A Training dimension
- An Employee dimension
- A fact table with each training session for each employee (including the date), with a Count measure and the associated fact dimension
Looking for syntax to add for each training session the date of the previous training session for the same employee ?
View 2 Replies
View Related
Jan 13, 2000
I've been having this huge problem with date and SQL 7, like lot of people. I use asp with the sql and trying to do this query where startdate should equal or smaller the date today, here are to queries that worked, but not anymore.
SELECT * FROM tbl_date WHERE convert(varchar(10), startdate, 101) = convert(varchar(10), getdate(), 101)
this query worked until the year 2000, the normal y2k bug
then this one, wich i saw here on messageboard:
select * from tbl_date where startdate =< '" & date & "'"
well this worked until today, when it just stop working
so does anybody know what to do?
hope to get answer on this
View 1 Replies
View Related
Mar 4, 2014
How can I calculate and return the previous Date at 18:00 Hours?
Here is a miserable attempt:
DECLARE @RunDate SmallDateTime
DECLARE @CurrentDate SmallDateTime
DECLARE @RunDateWoTime SmallDateTime
SET @CurrentDate = GETDATE()
SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate
SELECT @RunDate AS RunDate
-- Desired Result is the following:
-- 2014-03-03 18:00
View 9 Replies
View Related
Sep 29, 2014
I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
View 6 Replies
View Related
Jan 6, 2015
Given this table;
DECLARE @table TABLE (HolidayDate DATE, HolidayName NVARCHAR(50))
INSERT INTO @table
( HolidayDate, HolidayName )
VALUES ('2012-01-01','New Years Day'),
('2012-01-16', 'MLK Day'),
('2012-02-20', 'Presidents Day'),
[code]....
How to get a result set that shows a new column called PreviousHolidayDate with the corresponding holidays last years date?
HolidayDateHolidayNamePreviousHolidayDate
1/1/2012New Years DayNULL
1/16/2012MLK DayNULL
2/20/2012Presidents DayNULL
4/6/2012Good FridayNULL
5/28/2012Memorial DayNULL
[code]....
View 4 Replies
View Related
Dec 10, 2007
May I know how to use a "date" to select out previous 14 days record from the table? and find the duplicated records?
-- sort out duplicate order from tblOrder
Select * FROM tblOrder
WHERE DDay > @prmDDay("day", -14, getDate())
Group by DDay
Many thanks~~~~~
Fr New Learner
View 3 Replies
View Related
Jan 8, 2008
I'm trying to insert a date, I don't want the current time. It would be ok if it was 12:00:00. I have tried using the below bold area, but I get an error that the conversion is not correct.
Any ideas?
INSERT INTO tblpayments (rec_ID,client_ID,tranDate,tranAmount,DateEntered,EnteredBy)
SELECT rec_ID,client_ID,Convert(varchar(5), GETDATE(), 10) AS TranDate,Tranamount,DateEntered,EnteredBy
View 1 Replies
View Related
Jun 21, 2005
I couldn't think how to title this post so I apolgise for the poor title, maybe it's because it's getting late in the day and is too warm, maybe thats also why I can't think of the answer myself right now .
Anyway say I'm doing something where part of the data is a date (e.g. a counter) and then I want to get all the data from there between a set of two dates.
Now I can do that all fine, however what I want is 0's to be returned for dates where there is no data stored.
E.g.
At the moment I would get (for dates between 1st and 7th June):
Date Count
01-06-05 12
02-06-05 10
04-06-05 11
05-06-05 3
07-06-05 8
But I would like the dataset that is returned to be:
Date Count
01-06-05 12
02-06-05 10
03-06-05 0
04-06-05 11
05-06-05 3
06-06-05 0
07-06-05 8
Now as this DB should have data in it for every day of the year (but not always matching the where clause) I thought I could get the count back using a sub-query and a coalesce, but that seems rather hacky to me.
I'm on MS-SQL Server 2000, any help would be greatly appreciated.
-D
View 2 Replies
View Related
Mar 25, 2014
I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.
Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013
View 2 Replies
View Related
Mar 16, 2015
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
View 1 Replies
View Related
Jun 22, 2015
I have a list of patient encounter dates ordered by the date. I need to subtract the previous date in order to get the number of days between each date for the same patient.
create table TEST
(
MRN varchar(10),
EncDTTM datetime,
Sequence int
)
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-01-24','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-02-03','2')
[code]....
View 9 Replies
View Related
Jul 28, 2014
I need to get previous month data in the table based on current date.
In case of execution of each month, the data for previous month should come with date as between
create table TestDate
(Sno Int,
Name varchar(100),
DateofJoin datetime)
insert into TestDate values (1,'Raj', '2/21/2014')
insert into TestDate values (1,'Britto', '6/12/2014')
insert into TestDate values (1,'Kumar', '5/14/2014')
insert into TestDate values (1,'Selva', '6/27/2014')
insert into TestDate values (1,'Ravi', '5/2/2014')
insert into TestDate values (1,'Gopu', '6/2/2014')
/*
if I execute in month July ( ie: today)
select * from TestDate where dateofjoin between 1-june-2014 and 30-june-2014
Result
5 Ravi 2014-05-02 00:00:00.000
3 Kumar 2014-05-14 00:00:00.000
if I execute in month June
select * from TestDate where dateofjoin between 1-may-2014 and 30-may-2014
Result
6Gopu2014-06-02 00:00:00.000
2Britto2014-06-12 00:00:00.000
4Selva2014-06-27 00:00:00.000
/*
View 1 Replies
View Related
Oct 4, 2005
This seems really simple so I'm suprised I'm having so much trouble. Perhaps I'm just overthinking the whole issue. I'm trying to write a query that will extract a list of Users from a table that have logged in within the last 90 days. Sounds easy huh? I'm still stumped!Here's the basics of the attempted query:SELECT UserNameFROM UsersWHERE (LastLoggedInDate<= DATEPART(dd, GETDATE() - 90))Thanks!
View 1 Replies
View Related
Jan 14, 2015
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,
[code]...
View 2 Replies
View Related
Nov 4, 2015
I have a ssrs report which has a parameter start date.i have made 12 columns acc to client req which i cannot change:
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec
These columns are dynamic acc to start date for which i have used this exp for first cell:
=monthname(month(Parameters!Startdate.Value),true) and for next 11 cells accordingly.
These 12 columns are grouped quaterly which i have made statically again for clien req which i cannot change so they are grouped staticaly by me in report like:
q12015 |q2 2015 |q32015 |q42015
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec
exp for quarterly is :
="Q" & DatePart(DateInterval.Quarter, Parameters!Startdate.Value) & year(Parameters!Startdate.Value).
Now when i select my start date of months jan,apr,july and oct report work fine.... but when i select my start date months of feb,may,aug and nov then reportis something like: (lets say i choose feb)
q12015 |q22015 |q32015 |q42015
feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec|jan.
What is happening is in q1 feb,mar and apr are coming and what i want in q1 is jan,feb and mar.
And in q4 nov ,dec and jan is coming and what is want in q4 is oct,nov and dec.
So generally what is am asking is how to display jan in this condition in my first cell.same apllies for rest of months..
Similarly when i select my start date months of mar,june,sep,dec i want previous two months to be displayed.
View 5 Replies
View Related
Sep 24, 2001
BREAK THE DATE TIME FUNCTION DOWN INTO MINUTES...HELP!
View 1 Replies
View Related
Aug 1, 2014
I wanted to determine the date of the last Saturday with respect to the current date so that I could create a temp table with date ranges numbered 1-4 e.g. up to that date, post link below: URL....I now want to make this function that any time I pass it a day name (mon-sun) it will calculate the date of the previous (mon-sun) with respect to the current day. This date will then be the last date in my range.
So for example I run the function and pass it 'Friday' then I want last date in my range to be the 25th of July (today being Friday so need last Friday), I pass it Thursday I want it to give me the 31st of July etc. I need last date of that date but if I call Friday and today is Friday then need last friday; I think this is the trickier part of it.
This calculation I would image would need to use a combination of datediff dateadd etc. but really don't know how to do this dynamically.Currently I have a function that I pass the last date for the date range I want for my weeks range, I also give it the numbere of weeks I want in my range and also pass the date from the query that I want to know what week range it is in.
ALTER FUNCTION [dbo].[daterangeweeknumber]
(
-- Add the parameters for the function here
@refDate datetime,
@Weeks INT,
@lastdate datetime
]code]....
But now I want just to be able to pass it the day name - in this instance would be 'Wednesday' and want it to return date ranges with last date as Wed the 30th of July. Reason I want to use day name is that people will be running these reports on a certain day - for instance on Wednesday and last day to be Monday - and this will mean they do not have to keep changing the date in the call to function.
View 2 Replies
View Related
May 13, 2008
HiI have a problem to search between two dates, if I use Convert(varchar(10),ContactCreateDate,105) in a single textbow and for a single search date it is okay, but when I use the Convert(varchar(10),ContactCreateDate,105) against two textboxes it did't function.Can anybody help me ?MY codeif (TBContactSearchDateStart.Text.Length > 0 && TBContactSearchDateEnd.Text.Length > 0)
{
strWhere += " AND Convert(varchar(10),ContactCreateDate,105) BETWEEN '%" + TBContactSearchDateStart.Text + "%' AND '%" + TBContactSearchDateEnd.Text + "%'";
View 1 Replies
View Related
May 19, 2008
Hello All,i have a sql database with a table as follows. CustID (PK, Varchar(50) not null )Address ( VarChar(max), null)City ( VarChar(max), null)State( VarChar(max), null)Zip ( VarChar(5), null) ----------------------------- this is not working cuts off the leading zero like ( 01234) is shown as 1234DateShipped( datetime, null) --------------------- this display 05/18/2008 12:00AM. i just want to display (05/18/08).how can we do the above two. any suggestions please. i tried smalldatetime no luck . i appreciate it.Thanks.
View 3 Replies
View Related
Oct 7, 2015
how to hard code mention date range in my SP to get expected results in my query 01/01/2012 to 12/31/2012
DECLARE @ACCOUNT AS INT
DECLARE @POSTING_DATE AS DATETIME
DECLARE @FIRST_POSTING_DATE AS DATETIME
SET @POSTING_DATE = {?POSTING_DATE}
SET @ACCOUNT = {?ACCOUNT}
[code]...
View 3 Replies
View Related
Mar 29, 2007
Hi all,
I am trying to use the custom code in the report but I don't think I am understanding how this is being used.
I have a function to get starting months for a report parameter.
The function is below:-
--------------------------------------------------------------
Shared Function GetStartingMonths() as String
dim strDefault as string
dim CurrentMonth as String
Dim SqlString as String
'strDefault = Month(Now) & "/1/" & Year(Now)
CurrentMonth = "5/1/2002"
Do While CDate(CurrentMonth) <= Now
SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear"
CurrentMonth = dateadd("m",1,CDate(CurrentMonth))
if Cdate(CurrentMonth) = Now then
Exit Do
else
sqlString = SqlString & " Union "
end if
Loop
return SqlString
End Function
---------------------------------------------------------------
what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.
In the dataset, I had put the following expression
=Code.GetStartingMonths()
However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.
Am I doing something wrong here or is there a better way to doing this ?
Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?
thanks !
Bernard Ong
View 15 Replies
View Related
