Transact SQL :: Get Expiry Dates From CC
Sep 18, 2015
I have a Column CCExp Varchar(7) that stores data like below
NULL
'01/15'
'02/15'
'03/15'
'04/15'
'05/15'
[Code] ...
I am trying to get all expired cards which means, I am trying to get all cards which are behind sept 2015..like when I query I would like to get 8/15 and so on expired cards I am able to get the year , but with the below query I am getting all months less then 9 but i am missing 10,11,12 months cards values also I am having Conversion failed when converting the varchar value '#N' to data type int.
SELECT CustomerBillingInfoID ,
CCExp
FROM CustomerBillingInfo
WHERE LEFT( CCExp, 2) < MONTH(GETDATE())
AND RIGHT( CCExp, 2) <= RIGHT(YEAR(GETDATE()), 2)
and CCExp NOT LIKE '#%'
and CCExp not like 'NULL'
View 8 Replies
ADVERTISEMENT
Nov 18, 2005
SQL Server 2005 allows to use the EncryptByCert function to encrypt a Column, for example in an Instead-of Insert Trigger, and to use a DecryptByCert function to decrypt a Column, for example in a View.
This is indeed good news, but I have a simple :confused: question. To create a Certificate using the CREATE CERTIFICATE you have to give an EXPIRY DATE, or it will default to an expriry date of 1 year. How can one read the encrypted Column after the certificate has expired ?
Is there a way to renew a certificate without having to first decrypt all the fields and export them ?
View 3 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
Aug 4, 2015
I have a date comparison situation in which I will have a column with a date and will have another value containing a GETDATE() minus two weeks. I want to be able to compare both dates and get the MIN date between the two. I'm not sure how to use the MIN(Date) in this scenario since the comparison won't be between two different columns, but between one column and a random date generated by the GETDATE() minus two weeks.
View 6 Replies
View Related
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
Oct 15, 2015
TSQL statement, it should select the first date of the month at 11:59 PM as Firstdate and Closedate as 10 days later at 11:59 PM.
View 14 Replies
View Related
Nov 18, 2015
I have a table with 3 columns , let say (PatientID int, AppointmentDate date, PatientName varchar(30))
My source data looks in below way..
PatientID AppointmentDate PatientName
1 01/01/2012 Tom
2 01/10/2012 Sam
3 02/15/2012 John
I need output in below way..
PatientID AppointmentDate PatientName
1 01/01/2012 Tom (actual patient record)
null 01/10/2012 Tom
null 02/15/2012 Tom
null 01/01/2012 Sam
2 01/10/2012 Sam (actual patient record)
null 02/15/2012 Sam
null 01/01/2012 John
null 01/10/2012 John
3 02/15/2012 John (actual patient record)
I need t-sql to get above output. Basically the appointment dates are repeatedly assigned to each patient but only difference is patientid will be not null for actual patient record.
Create table sample (PatientID int null, AppointmentDate date null, PatientName varchar(30) null)
Insert sample values (1,'01/01/2012' ,'Tom'),
(2,'01/10/2012','Sam'),
(3,'02/15/2012','John')
View 2 Replies
View Related
Jun 21, 2015
I have a sql code like this:
declare @currentDate datetime,@enddate datetime;
select @currentDate = '06/1/2015';
select @enddate='06/05/2015'
while @currentDate <= @enddate
begin
-- do whatever is needed
select @currentDate = dateadd(DAY,1,@currentDate);
select @currentDate as currentdate
end
In result am not getting the current date. am getting date from 06/02/2015.i want to get records from 06/1/2015 to 06/05/2015. total 5 records..but now i am getting only 4 records.
How I can get full records.
View 18 Replies
View Related
Aug 22, 2015
i have a table like below
CREATE TABLE #Test (FromDate DATE,ToDate DATE)
insert into #Test VALUES ('2015-08-08','2015-08-11')
insert into #Test VALUES ('2015-08-13','2015-08-16')
insert into #Test VALUES ('2015-08-19','2015-08-21')
SELECT * from #Test
drop TABLE #Test
i need to display the dates as single column between from and todate.my expected result is like below
CREATE TABLE #Result (ResDate DATE)
insert into #Result VALUES ('2015-08-08')
insert into #Result VALUES ('2015-08-09')
insert into #Result VALUES ('2015-08-10')
insert into #Result VALUES ('2015-08-11')
[code]....
View 2 Replies
View Related
Aug 20, 2015
Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
View 14 Replies
View Related
Sep 25, 2015
I want a difference in days
Select datediff(dd,Target_Date,Achv_Date)
Now , checks are
1] when target date greater than achv_Date the difference should be greater than 0
means for FileID 77608
Select datediff(dd,'2015-09-24 00:00:00.000','2015-09-24 10:42:32.823')
i am getting -6 it should be 6 cant switch Target_Date and Achv_Date in datediff else i will get opposite result in first four records basically, i want a two column TAT and Status beside achv_date based on the values of two dates difference see above ..and also want a result of (No. of Yes in status / No. of Files that has achv_date )i.e. result= (7/8) = 87%
View 6 Replies
View Related
May 7, 2015
In a stored procedure I have 2 dates that I need to combine parts of into a third date. I need the year from date1 and the month/day from date2.
Date1 = '2015/6/1'
Date2 = '2016/1/1'
Date3 needs to be '2015/1/1'
I have been messing around with datepart and dateadd with no luck.
View 3 Replies
View Related
Apr 24, 2015
What I want to see is how to show PO's who's due dates are > three
Select * from mytable
where myorderstatus = 'onorder'
This is my duedate format and what I want is any that past that date over 3 days
2014-08-11 00:00:00.000
View 11 Replies
View Related
Oct 13, 2015
We've researched this, and some people are stating convert to nvarchar first. I'd like to keep the date as a date.Here is how I am trying to call it:
Declare @FromDate as date
Declare @ToDate as date
Set @FromDate=Convert(date,'09-01-2015',110)
Set @ToDate= Convert(Date,'10-13-2015',110)
Select @FromDate
[code]...
How can I keep the date as a date and still pass it to stored procedure.
View 3 Replies
View Related
May 21, 2015
Have this table
ACCOU NAME NAME TODATE ID EDUCAT EXPIRYDATE
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
Need to get it like
ACCOU NAME NAME TODATE ID EDUCAT EXPIRYDATEstring
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.
View 11 Replies
View Related
Oct 18, 2015
IF OBJECT_ID('tempdb..#OverLappingDates') IS NOT NULL
DROP TABLE #OverLappingDates
CREATE TABLE #OverLappingDates
(
MemberID Varchar (50),
ClaimID Varchar(50),
StartDate DateTime,
EndDate DateTime
)
[Code] ....
I need to select only records where dates are over lapping for the same memberid...For this scenario i need an output First 2 records (MemberID 1 has 2 claiims overlapping dates).
View 2 Replies
View Related
Jun 3, 2015
I have a simple following table which is having only one date column.
CREATE TABLE TEST_DATE
(
InputDate DATE
)
GO
INSERT INTO TEST_DATE VALUES('01-01-2015')
INSERT INTO TEST_DATE VALUES('06-25-2015')
INSERT INTO TEST_DATE VALUES('11-23-2014')
GO
SELECT * FROM TEST_DATE;
And the expected out put would be as follows:
I want to derive a Four Quarter End Date based on Date selected.
For Example if i select 01-01-2015 then
First Quarter End Date would be Previous Quarter End Date
Second Quarter End Date would be Current Quarter End Date
Third Quarter End Date would be Next Quarter End Date
Fourth Quarter End Date would be Next +1 Quarter End Date Like that
View 9 Replies
View Related
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
View Related
May 23, 2015
I need to calculate the amount of time between each visit. I am pulling the Row Number for my visits and now I need the date span that goes between each day. I also need a new column that returns a Yes or a No if the date span exceeds 3 years.
SELECT
ROW_NUMBER ( ) OVER ( PARTITION BY pv.PatientProfileId ORDER BY pv.Visit ASC ) AS RN
, CONVERT ( VARCHAR ( 20 ) , pv.Visit , 101 ) AS Visit
, pv.TicketNumber
, vstatus.Description AS VisitStatus
, doc.ListName AS Doctor
[Code] ....
View 3 Replies
View Related
Oct 6, 2015
I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table. The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?
CREATE TABLE t1 (d1 date,d2 date)
go
create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT
AS
BEGIN
IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2)
RETURN 1
RETURN 0
END
go
alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0)
go
insert into t1 values ('2015-01-01','2015-02-01')
insert into t1 values ('2015-01-15','2015-02-15')
--BOTH inserts result in the following message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".
drop table t1
drop function dbo.dt_overlap
View 14 Replies
View Related
Sep 21, 2015
I put this series of select statements to verify that the BETWEEN statement is working properly. I should always get “Between” below.
SELECT
CASEWHEN'1/1/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END
SELECT
CASEWHEN'1/31/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END
[Code] .....
View 4 Replies
View Related
Sep 10, 2015
I want generating Valid date ranges from any list of dates.
The List of Dates could be generated from the below TSQL -
SELECT '2015-06-02' [Date] UNION ALL
SELECT '2015-06-13' UNION ALL
SELECT '2015-06-14' UNION ALL
SELECT '2015-06-15' UNION ALL
SELECT '2015-06-16' UNION ALL
SELECT '2015-06-22' UNION ALL
SELECT '2015-06-23' UNION ALL
SELECT '2015-06-24'
And the expected output should look like -
SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL
SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL
SELECT '2015-06-22' FromDate, '2015-06-24' ToDate
View 2 Replies
View Related
Nov 17, 2015
My scenario is: a person has many events, all based on a date. I need to aggregate the person to show min and max dates for a period, the period being defined as ending when there is not an event following the next date.
DECLARE @Events TABLE
(
PK_EventINTIDENTITY(1,1) PRIMARY KEY
,FK_UserINTNOT NULL
,EventDateDATENOT NULL
)
DECLARE @User TABLE
[Code] ....
I would expect the groups to look something like below:
Is this where a recursive CTE may be used?
View 6 Replies
View Related
May 6, 2015
I am currently working on a T-Sql query(Sql server 2008) to calculate total no of days between date ranges by year
Table:
Start Date End Date
01/01/2013 04/30/2014
11/01/2014 05/31/2015
06/01/2015 12/31/2015
My expected result.
2013 - 365
2014 - 181
2015 - 365
Note:
Date range can span b/w multiple years
Date ranges will not overlap
I just want the total number of days covered by the range for each year.
Is there any simple way to do this calculation.
View 9 Replies
View Related
Dec 2, 2015
This question is extension from the topic Updating table Rows with overlapping dates: [URL] .....
I am actually having a table as following:
Table Name: PromotionList
Note that the NULL in endDate means no end date or infinite end date.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-28 0
2 1 2015-04-23 NULL 0
3 2 2015-03-03 2015-05-04 0
4 1 2015-04-23 2015-05-29 0
5 1 2015-01-01 2015-02-02 0
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
Flag = 1, success merge row. Flag = 2, fail row
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 NULL 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-29 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
Note that above is just sample Data. Actual data might contain thousands of records and hopefully it can be done in single update statement.
Extending from the above question, now two extra columns has been added to the table, which is ShouldDelete and PromotionCategoryID respectively.
Original table:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 2015-05-28 0 Y 1
2 1 2015-04-23 2015-05-29 0 NULL NULL
3 2 2015-03-03 2015-05-04 0 N NULL
4 1 2015-04-23 NULL 0 Y 1
5 1 2015-01-01 2015-02-02 0 NULL NULL
Should Delete can take Y, N and NULL
PromotionCategoryID can take any integer and NULL
Now it should be partition according with promotionid, shoulddelete and promotioncategoryID (these 3 should be same).
By taking the min date and max date of the same group, the following table should be achieve after the table is updated.
Final outcome:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 NULL 1 Y 1
2 1 2015-04-23 2015-05-29 1 NULL NULL
3 2 2015-03-03 2015-05-04 1 N NULL
4 1 NULL NULL 2 Y 1
5 1 2015-01-01 2015-02-02 1 NULL NULL
View 2 Replies
View Related
Jun 15, 2015
I'm trying to add random dates to date column in existing table, but these need to be week days (Mon-Fri).I'm a beginner in TSQL, worked with MS Access many years - in Access I used to do something a bit different:
DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
Table had ID, I gave a date it would start from (31/08/2015) and then range of ID to apply new date:
UPDATE table1 SET table1 .date = DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
WHERE (((table1 .ID) Between 1 And 5456));
This was applying random dates in range of 31/08/2015 + 5 days, so I could give a starting date of Sunday to get random dates populated over given IDs from Monday to Friday.Now, how can I do it in TSQL?I have a table with ID and dates column. I would like to apply new random dates from some range, but making sure they will be week days.
View 3 Replies
View Related
Oct 14, 2015
Here I have 2 Dates. CreatedDttm & ModifiedDttm.
I want - DATEDIFF(Day,CreatedDttm,ModifiedDttm) and I have to exclude the Weekend days from that query result.
View 10 Replies
View Related
Oct 13, 2015
The data I have is as follows -
ID1 ID2 Date Action
100 500 09/08/14 Open
100 500 09/24/14 Close
101 510 07/10/15 Open
101 510 07/19/15 Close
The output I want in a single result set is -
ID1 ID2 Open_Date Close_Date
100 500 09/08/14 09/24/14
101 510 07/10/15 07/19/15
Any way to do this in T-SQL .
View 10 Replies
View Related
Jun 21, 2007
Hi All,I have a job coming up which involves me creating a vacancy system for our website.I (think) I'll only need one table:ID (PK)JobTitleJobLocationSalaryHoursJobDescriptionValidFrom (Date/Time)ValidTo (Date/Time)I'll display this data using a C#.Net repeater but I was wondering how I can automatically strip the page of the jobs that have gone past their expiry date? Is is straightforward to achieve?Thanks in advance,Brett
View 2 Replies
View Related
Dec 20, 2005
RETAINDAYS option in BACKUP command.
SqlServer2000 (standard) on Win xp server.
Old backups are not being removed from backup sets.
I've created logical dumpdevices which maps to a physical file on disk - using sp_adddumpdevice. Regular, scheduled backups are successfully written to the backup sets (Complete, differential & Tlog) - This works fine and I can restore from them OK.
I've set the RETAINDAYS option of BACKUP command to keep backups for varying periods (eg full = 8 days, diff = 2 days, and Tlog = 1 day).
When the backups are viewed by 'drilling down' thru >Backup > open backupdevice > view contents.... all previous backups are still available even though their expiry date has passed.
The backup set files (dumpdevice files) are getting inexorably bigger and will eventually fill the available disks !
I don't want to delete or overwrite the entire contents of the backup set - just those backups more than 8 days old.
I've run sp_configure 'media_retention' 1 RECONFIGURE WITH OVERRIDE
but the old backups are still there.
dbs are getting ever bigger (high activity OLTP system) currently 2 production dbs totaling 50 GB.
Is this a bug? - ie. the option to set a retention period even though I'm writing to a logical 'dump device' rather than a normal *.BAK / *.TRN file?
or Am I doing something wrong?
HELP.
Cris Yarker
email to crispin@paymentsystemsltd.com
View 3 Replies
View Related
Feb 24, 2004
Hi all,
The company that I work for has an internet application that has been running in our client's environment for 2 years.
The SQL box is a quad Xeon 750 with 4 gig of ram and over 50gig of disk. This box has 4 databases that are used for the application. The primary database is about 1.5gig in size (.mdf file) the others are relatively small.
The box is queried from a separate windows 2000 server box with our COM based application running under IIS.
The boxes are located on the same switched LAN.
Up until recently the application has been running fine, but all of a sudden we have had masses of database time outs and the processor utilisation is always 80-100. In turn these time outs crash IIS eventually.
I Understand all of the ideas about increasing the timeout in a command object, etc.
I have monitored blocking on the SQL server and over a 30 minute period of live usage I saw 1 block and 0 deadlocks.
In addition to the above often our application gets an imediate time out error, hence after no time.
We have also monitored the network bandwidth between the two boxes and they only use about 30% on average of available bandwidth.
My 1st question is - Does SQL give a time out error (hence refuses connections) if it is running at 100% and cannot handle any more load??
My 2nd question is, In other peoples experience is the box we are using powerful enough.
My 3rd question is, Can the network have an effect on the time outs even though there is plenty of bandwidth.
Thank you in advance for your time in reading this and I appreciate any input that you may have.
Simon Gratton
Senior/Lead Developer
Fastrack Software Ltd.
View 2 Replies
View Related
Aug 15, 2006
Hi,
I created a master key and
CREATE CERTIFICATE mycer
WITH SUBJECT = 'data encrypt',START_DATE = '12/1/2005',EXPIRY_DATE='7/9/2006'
GO
CREATE SYMMETRIC KEY mySymetric WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE mycer,password='mypass';
GO
and i created a table with varbinary field and i added 2 encrypted records and i got it properly
i need to update the expiry data for my certificate , so i dropped the symetric key and their certificate and run the following
CREATE CERTIFICATE mycer
WITH SUBJECT = 'data encrypt',START_DATE = '12/1/2005',EXPIRY_DATE='12/12/2006'
GO
CREATE SYMMETRIC KEY mySymetric WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE mycer,password='mypass';
GO
i tried to get the added record , i got a null value somthing is happened while dropping the key for the encrypted data??
any help ?
Jim
View 5 Replies
View Related
Aug 27, 2015
I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
View 9 Replies
View Related
