Transact SQL :: Pull Dates From Database And Then Compare
Jun 10, 2015
I need to pull dates from a DB2 database via TSQL (Linked server - IBM DB2 for i IBMDASQL OLE DB Provider) and compare it to today for a less than or greater than type comparison.
Database: DB2, Customer information housed here
Columns:
UTOFMM - Month (2 character, numeric)
UTOFDD - Day (2 character, numeric)
UTOFYY - Year (2 character, numeric. Problem: years from 2000 to 2009 are stored as 0, 1, 2, ... etc)
UTOFCV - Century Value (2 char, numeric. 0 = before 2000, 1 = in or after 2000)
I need to concatenate the date to be "sql" friendly, and then compare to today's date. It's to find any customer with date values in the fields above, and then differentiate between dates before today and after today.Here is the snippet of what I'm trying to fix. This portion of a nightly job is just checking for <u>any</u> value in the UTOFMM column of the current record.
Add Customer ID
Update [responder].[Temp_RX_CUSTOMERS]
set CustomerID = lf.UTCSID
from [responder].[Temp_RX_CUSTOMERS] LEFT Outer Join
[HTEDTA].[THOR].[HTEDTA].UT210AP lf ON [responder].[Temp_RX_CUSTOMERS].LocationID = lf.UTLCID
where lf.UTOFMM = 0
GO
View 4 Replies
ADVERTISEMENT
Oct 10, 2015
I have three date columns startdate,enddate,rmd
I need to write a query like get all the rows by comparing rmd with system todaydate and these must be with in startdate and enddate
id startdate enddate rmd
1 10-oct-15 16-oct-15 11-oct-15
2 11-oct-15 14-oct-15 11-oct-15
3 09-oct-15 11-oct-15 10-oct-15
4 07-oct-15 08-ot-15 07-oct-15
5
if sysdate(today) is 11-oct-15 then i need to get all the rows of 1, 2,3
View 5 Replies
View Related
Jul 11, 2014
I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
How to get these tables included in the project .
View 1 Replies
View Related
Sep 23, 2015
I have 2 database, databaseA and DatabaseB with same table structure.
I change lot of table structure in databaseB.
So i want compare what field that i add or delete in databaseB.
it's like what field there are in databaseB and not found in databaseA or nothing in databaseB but exist in databaseA.
How can i do that?
View 7 Replies
View Related
Feb 14, 2007
I want to pull dates from my database that are between to set dates i have written a query that does this which looks like this:
"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('12/02/2007', 2) + '/' + SUBSTRING('12/02/2007', 4, 2) + '/' + RIGHT ('12/02/2007', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('14/02/2007', 2) + '/' + SUBSTRING('14/02/2007', 4, 2) + '/' + RIGHT ('14/02/2007', 4)))"
This works when the dates are included in the statement but when i try and use parameters to pull them in like this:
"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('@Choice1', 2) + '/' + SUBSTRING('@Choice1', 4, 2) + '/' + RIGHT ('@Choice1', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('@Choice2', 2) + '/' + SUBSTRING('@Choice2', 4, 2) + '/' + RIGHT ('@Choice2', 4)))"
I then recieve the following error message why is this?
ERROR -------------------> Syntax error converting datetime from character string.
Any Help would be greatly appreciated thanks
View 2 Replies
View Related
Feb 4, 2008
I am pulling data that shows appt information on a patient:
FName,LName, PhoneNumber, ApptDate, ApptTime, and Status that equals Active. I need to pull this info everyday. How can I set this up in a stored procedure to pull all this information automatically on a daily basis where it pulls by Appt Date that is 2 days greater than the current date?
Thanks!
Thanks!
Lisa
View 9 Replies
View Related
Dec 14, 2006
I need to compare tow dates DateField1 and DateField2 and find number of hours between these two dates. Then I need to deduct non-business days and hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from this and find net hours. How can I do this?
View 7 Replies
View Related
Apr 13, 2008
Hi,I need to compare Todays Date with a Date in the Table (DateExpired)to see if the membership expired. How do I do that? thanks
SELECT DateExpired FROM Member
View 2 Replies
View Related
Feb 4, 2002
how to compare the dtaes for the follwing
SELECT COL1,COL2 FROM TABLE1
WHERE WORKDATE >01/01/2002
THE WORKDATE IN THE TABLE IS HAVING DATETIME DATATYPE.
I DONT WANT CONSIDER THE TIME OF THE DATEPART.
I JUST WANT TO COMPARE THE DD-MM-YYYY.
PLEASE HELP ME IN THAT.
THANKS IN ADVANCE
View 1 Replies
View Related
Apr 9, 2007
Hi,
I am new to SQL and I have a problem to deal with dates.
I have a field called LogDate (smalldatetime) and default value getdate(). The date is added immediately when a visitor visits a page of my site.
The problem is that I cannot retrieve the no of the visitors for today using getdate(). I use the following query but I am sure that something missing.
SELECT Count(*) As DailyVisitors
FROM stats
WHERE LogDate = CONVERT(varchar(10), GETDATE(), 103)
Please help
Denis
www.tabletennis.gr
View 9 Replies
View Related
Feb 28, 2008
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev
FROM dbo.EntityDef INNER JOIN
dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN
dbo.CurrentDate INNER JOIN
dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate
WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')
ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate
I appreciate all your help
Thanks
View 13 Replies
View Related
Jun 23, 2006
Hi,
I wanted to display the createDate provided by the asp membership table.
my where clause is this .. WHERE CreateDate = GetDate()
I have create an account today but when i run my select statement with this where clause, the result doesnt show up. It seem like they compare the time as well. How can i make the GetDate to compare only the dates but not the time?
View 1 Replies
View Related
Dec 13, 2006
sorry i posted this in the tsql forum by accident, here's a repost
I've got a query that I need help with. This is a simplified version of the table I have.
sellerID, orderID, time1, time2, time3, time4
1, 1b1, 11:30 11:35 11:40 11:45
1, 1b2, 11:50 11:55 12:00 12:05
1, 1b3, 12:10 12:15 12:20 12:25
2, 1c1, 11:20 11:25 11:30 11:35
2, 1c2, .... and so on
What I want is a datediff between time4 of the first OrderID to time1 of the next orderID within the same sellerID.
What I want is the following output
sellerID, orderID, timeDif1(time4-time1 of nextrow), timeDif2(time2-time1), TimeDif3(time3-time2), timeDif4(Time4-time3)
1, 1b1, 10mins, 5mins, 5mins, 5mins
Any help would be appreciated!!
THanks
View 1 Replies
View Related
Jul 5, 2007
need to look through hundreds of rows and compare two columns, and pull out rows where the date difference is 7 days
problem is one column stores the date as a string and values in that column look like this:
01/12/2007 10:15 pm
or
01/12/2007
the other column stores the date as seconds since 1/1/1970, which is good and easy to work with..
how do I write the select?
any ideas
thnx
tony
View 3 Replies
View Related
Jul 5, 2007
How can I check if:
Date1 is newer than Date2?
Thank you!
View 5 Replies
View Related
Jan 9, 2008
I need to compare several dates in a single record and determine the maximum value. Not sure how to do this in TSQL. Something like this (though I obviously can't use the MAX function because):
SELECT MAX(datefield1,datefield2,datefield3) as latestchange,jobnum
FROM table1
Anyone have a function that will do this? Or is there one built in?
Thanks!
Brian
View 11 Replies
View Related
Mar 20, 2008
Hi:
Well, I am venturing into new territory for me. I'm very illiterate when it comes to SQL Server and so I need assistance. I have the beginnings of my stored procedure, which is supposed to compare two dates/times and If they are not equal I need to kick off a DTS Package.
So, here's what I have so far (it returns two dates like I would expect):
CREATE PROCEDURE usrCompareDataDownload
AS
BEGIN
SELECT MAX(ASP_ZZ_CHNG_TMST) FROM tbl_MaterialWeeklyData;
SELECT MAX(ZZ_CHNG_TMST) FROM TV_ASP_DPUL_WKLY;
END
GO
Thanks,
Bob Larson
View 5 Replies
View Related
Feb 15, 2007
This is probably a problem with a pretty simple solution but i can't find the right control/data flow item to handle it
Scenario.
I determine the database date for my source data for a set of ETL jobs via a piece of SQL - this gets passed to a master package variable which is subsequently to be used as the "Load date" of the resulting child package ETL routines. However I only want the packages to run if the LoadDate has either not been run before or is the next one in the DW sequence.
To check for this, In my data warehouse I also have a table called Import_Registry where the date of each upload is stored at the end of the daily ETL routines. So I can obtain the potential NextUpload date via this bit of SQL script.
SELECT DATEADD(day, 1, MAX(Upload_Date)) AS NextUpload FROM Import_Registry
Problem. I need to compare these two dates (the source db date & the DW next upload date) to see if they match. If they do match, then I run all the ETL packages using the date. If they do not match, say for example if the source database date is less than the "NextUpload" date I want to exit the routines "gracefully" and log the failure.
How do I get this working - can't seem to get my head around how I can compare the 2 dates ?
View 5 Replies
View Related
Nov 5, 2015
I have a table that i'm trying to pull the first transaction and last transaction per prodid. The first transaction, the ctrid should be 'TOPPEntry'.
sample raw data result
prodid--------lineid------itemid---------ctrid----------createddate--
=================================================================
TOP02296228--Line15-----TTC3071-IR------TOPPEntry------2015-01-03 07:45:31.000--input
TOP02296228--Line15-----TTC3071-IR------TOPCBFG--------2015-01-07 16:18:26.000--fg--done processing
TOP02296229--Line15-----TTC3071-IR------TOPPEntry------2015-01-04 07:45:31.000--input
TOP02296229--Line15-----TTC3071-IR------TOPLens--------2015-01-05 12:12:31.000--wip--wip means still in process
TOP02296230--Line15-----TTC3071-IR------TOPPEntry------2015-01-05 08:45:31.000--input
TOP02296230--Line15-----TTC3071-IR------TOPCBSCP-------2015-01-06 14:18:42.000--scrap--done processing
[code]...
View 12 Replies
View Related
Feb 18, 2015
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
View 9 Replies
View Related
Apr 8, 2015
I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.
Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000
View 2 Replies
View Related
Apr 21, 2015
I have two tables and I am trying to pull a value closest to another date. There could be instances where the Eval_date in TableA matches the date in TableB so how would that be handled?
TableA
PA_IDEval_Date
12015-01-08 00:00:00.000
22015-01-25 00:00:00.000
TableB
IDPA_IDDate Stat
112015-01-15 00:00:00.000 14
222015-01-28 00:00:00.000 8
312015-01-16 00:00:00.000 2
422015-01-029 00:00:00.000 3
512015-01-09 00:00:00.000 11
612015-03-14 00:00:00.000 14
Expected Results
PA_IDDate Stat
12015-01-09 00:00:00.000 11
22015-01-28 00:00:00.000 8
View 8 Replies
View Related
Sep 21, 2015
I have three tables:
"PaymentsLog"
"DatePeriod"
"PaidOrders"
As per below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
View 3 Replies
View Related
Aug 7, 2015
I'm trying to pull all records from one table and just a single record from another. I have this join, (see below). It works ok, but the problem is if a blog record doesn't have a corresponding image record it doesn't return. The end result should be the blog record and a single corresponding image record. But always a blog record.
SELECT
[Blogs].[ID],
[Blogs].[BlogTitle],
[Blogs].[BlogType],
[Blogs].[BlogText],
[code]...
View 6 Replies
View Related
Sep 14, 2015
I have 3 tables.
Table 1:
ID Name Description
1 ABc xyz
2 ABC XYZ
Table 2:
RoleID Role
1 Admin
2 QA
Table 3:
ID RoleID Time
1 1 09:14
2 1 09:15
1 2 09:16
Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.
View 4 Replies
View Related
Jun 16, 2015
I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?
select id, list_date
from tableA
where list_date > DATEADD(year,-1,GETDATE())
View 5 Replies
View Related
Jul 20, 2005
I need the maxvalue of 2 colums for each rowSelect date1, date2, maximum(date1,dat2) as datemax.Is this simple to realize ?date1 and date2 can be NULL.something like this:select date1,date2, maxdate =CASEwhen date1 is null then date2when date2 is null then date1elseif date1 > date2 then date1else date2from xythanks for your helppaul
View 4 Replies
View Related
Nov 17, 2015
I would like to compare rows from 2 tables.
For ex:
Table1
Col1 Col2 Col3
1 a aa
2 b bb
Table2
Col1 Col2 Col3
1 a aa
2 b bb
Notice Table2.Col2 first row has an extra space " a", where Table1.Col2 value is "a".I need to compare all the columns, not just 1 column (any of the column can be different). Do we use CHECKSUM for this, or is there a better way to compare the 2 tables (in this case resulted in Table2 row 1 col 2 being the difference ?
View 10 Replies
View Related
Sep 11, 2015
i designed a database for eshop.
i have two table one of them is products and it has id,code,name columns and the other one is option with id,proid,option,optiondes column. and there is a relationship with id from products to proid in option table.
i want to make query that results is compare two or more products with the same option column.
View 2 Replies
View Related
Jul 16, 2015
I downloaded a day's worth of data into a single table first and named it as Full_Day_Data_Table.(10,000 records)
Later for some other purpose, downloaded the same day's data into five small tables.
Now when I compare the total row count of Full_Day_Data_Table vs Sum of row counts of five small tables, the row count does not match.
So, I would like to know the records that exists in Full_Day_Data_Table and not in small tables.
View 5 Replies
View Related
Aug 12, 2015
I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.
I'm looking for added columns in dbase 1.
Is there a system T-SQL script that can be used for this?
View 4 Replies
View Related
May 29, 2015
I want to sum all the dates and show the total date sum. How can I do that in SQL
For these dates
00:35
00.40
00.55
03.50
View 5 Replies
View Related
Oct 20, 2015
I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure
DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE)
INSERT INTO @A VALUES (1,'AAA','2015-10-01')
INSERT INTO @A VALUES (1,'BBB','2015-10-01')
INSERT INTO @A VALUES (1,'CCC','2015-10-01')
INSERT INTO @A VALUES (1,'AAA','2015-10-05')
INSERT INTO @A VALUES (1,'DDD','2015-10-01')
INSERT INTO @A VALUES (2,'AAA','2015-10-02')
INSERT INTO @A VALUES (2,'BBB','2015-10-02')
INSERT INTO @A VALUES (2,'CCC','2015-10-02')
INSERT INTO @A VALUES (2,'DDD','2015-10-02')
[code]...
how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.
View 4 Replies
View Related
