Date Checking Prob

Oct 5, 2000

I am facing a funny problem. I am doing a select to get records with "ExitDate" greater than "FromDate".
However, when I run the query, it is also returning the "FromDate".

SET @FromDate = '3/31/2000'
SELECT IndividualID, ExitDate FROM clientserviceprogram
WHERE exitdate IS not null
AND EXITDATE > @FromDate

I am still getting records with ExitDate = '3/31/2000'.

What am I missing out on?

Thanks in advance!

View 2 Replies


ADVERTISEMENT

Prob With SQL Script That Work With Date/Time Abd Boolean

Jul 23, 2005

I convert my DB from Access to SQL Server ,When i try to run my script (in ASP) it's not store the data atDate/Time field!!!And i have troble with insert the value TRUE in Sql Server (it's workwith access)I want to know if i need to change my scripts(in ASP) or the type offields?Please, HelpBest regards.

View 2 Replies View Related

Checking For Date Conflict

Nov 2, 2005

Although I am familiar with SQL commands I have never worked with SQL server before and find myself at a loss for which direction to take. I have a database for equipment reservations. I need to write a procedure to notify the end-user if a specific piece of equipment is already checked out during the time frame requested. I'm reading up on stored procedures and triggers and not sure which one (if either) is appropriate to use. Any suggestions? Thanks,

View 1 Replies View Related

SQL Error, Checking For A Date

Nov 24, 2007

Hello,

i have a very simple table called room....

Room_ID Date
1 20/11/2007 00:00:00

and i have a very simple SQL SELECT statement...

SELECT * FROM room WHERE date = '20/11/2007 00:00:00'

i have tried several variations of the query but i either get an error or a null dataset.

can anyone explain where im going wrong ?? i have read about casting but still i cannot return a dataset where i have a date in the statement.

Any help is much appreiciated !!


Truegilly

View 1 Replies View Related

Checking If Date Ranges Overlap

Nov 6, 2013

Just want to check if my query is the standard way to check if date ranges overlap in a price table as I need to check any that overlap as I can't have two prices on one day.

For example if in a table there was:

Product TROUSER Colour BLUE
Start Date 01-NOV-13 End Date 20-NOV-13 Price £20.00
Start Date 10-NOV-13 End Date 12-NOV-13 Price £18.00
Start Date 21-NOV-13 End Date 25-NOV-13 Price £15.00

The top two overlap.I'm doing this which is giving me nothing returned which I'm hoping means I have no overlapping date ranges:

SELECT a.[PriceList]
,a.[ProductID]
,a.[Colour]
,a.[Start Date]
,a.[End Date]
,a.[Product Price]

[code]....

View 5 Replies View Related

Checking Credit Card Date ....?

Dec 17, 2007



Hi friends,

I am new to SQL developement ( am .Net developer). I need to implement a stored proc in sql 2005 on CC expiration task.

I have CC exiration date.
By using expiration date (like '01/08'),

Conditions are:

1. If CC expiring within 31 days. Stored proc will write the Customer data in EmailNotification table with message type =1. (Customer will not get second notification until 14 days before expiring)

2. If CC expiring within 14 days. Stored proc will write the customer data in EmailNotification table with message type =2. (Customer will not get second notification until 11 days before expiring)

3. If CC expiring within 11 days. Stored proc will write the customer data in EmailNotification table with message type =3. (Customer will not get second notification until 2 days before expiring)

4. If CC expiring within 2 days. Stored proc will write the customer data in EmailNotification table with message type and update orderStatusID to On Hold CC Expired in order table.

My Tables are

1.

TABLE [dbo].[Order](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[CustomerCode] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateOrdered] [datetime] NOT NULL,
[OrderTypeID] [int] NULL,
[OrderStatusID] [int] NULL,
[LastProcessDate] [datetime] NULL,

2. CREATE TABLE [dbo].[Payment](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[OrderPaymentType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PaymentType] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditCardNumber] [varbinary](max) NULL,
[Last4DigitsOfCreditCardNumber] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExpirationDate] [varbinary](max) NULL,
[CVV2] [varbinary](max) NULL,
[Amount] [numeric](18, 2) NOT NULL

3. CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerCode] AS ([dbo].[udf_FormatCode]('RS',[CustomerID])),
[Email] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiddleName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateModified] [datetime] NOT NULL,
[MembershipTypeID] [int] NULL


Please atleast basic structure of stored proc code. It will greatful to me.

Thanks in advance

srikanth

View 3 Replies View Related

SQL 2012 :: Checking If A Date Exists In Particular Daterange

Jan 6, 2015

I have a table called as Events and below are its columns

ID int
EventFromDate datetime
EventToDate datetime
EventDesc nvarchar
IsHoliday bit

This is a master table where the admin would enter the Events/Holidays for the entire year.

The data would be as below:

IDEventFromdateEventTodateEventDesc isHoliday
126-01-201526-01-2015RepublicdayYes
230-01-201531-01-2015TeamOutingNo
301-05-201501-05-2015Labour day Yes

Now, suppose a employee applies leave on 26/01/2015 to 26/01/2015 then it should not insert into table and return a value "Not updated"

How to handle the scenario if a employee applies leave between the range 23/01/2015 to 27/01/2015, since 26/01/2015 is a holiday in between. how the data can still be inserted excluding 26/01/2015

Can we exclude a non-working day or a sunday.

Leavedetails table to insert leaves applied by employee is as follows

LeaveDetailID int
LeaveTypeId int
FromDate datetime
EndDate datetime
Remarks nvarchar

View 0 Replies View Related

SQL Server 2012 :: Date Range Checking In Table

Mar 16, 2015

I have a table with EmployeeID, StartDate, and EndDate with a PK of EmployeeID, StartDate. How can I check to see that there's no overlap for StartDate and EndDate for a given employee? That is, on any given day there must only be 1 row for an employee where Getdate() is Between StartDate and EndDate. For an active employee their EndDate is set to 06/06/2079.

I've tried it using Row_Number() with Over() but am returning too many rows indicating overlap when none exists.

View 7 Replies View Related

DTS Prob

May 17, 2001

I have to transfer some data from my SQL Server 7.0 database on NT Pro to a Unix server as a text file. This has to be scheduled as a weekly job.
I can use DTS to transfer data into a text file , but is there a way by which i can export this file to a Unix server also ?
Any suggestions would be appreciated.

Thanks!

View 3 Replies View Related

Hi My Db Is Prob

Aug 29, 2007

Hi friends,


My database had this require ment

Every day my database 12;00pm we will take fullbackup
1:00 O clock differntial backup
2:00 O clok again on diifferntial backup
Every 15(fifteen)Min we will take Transaction log backups
My database is crashed on 2:00 clock we will not take 2:00 clock Tlog backup
we have taken only 1:45 Tlog backup.

Here my database is loss 1:45 to 2:00 clock data

we cannot take this 15min Tlog backup

Any body plz give suggestion on how to recover this 15min data plz help me it very urgent

Regurds
subu

Meti BEST OF THE BEST

View 1 Replies View Related

HAVING Prob !

Jun 2, 2006

Bonjour,CREATE TABLE [dbo].[MAND]([Mat] [varchar](5)[Dur] [varchar](1)) ON [PRIMARY]Mat Dur16030d16030i31217i10000d12000i10000d31217d35000d36000i35000dJe voudrais avoir le resulat suivant (i need this result) :10000 d35000 dCar ils ont tous les deux "d". J'ai beau faire un regroupement (groupby) par Mat avec un having çà ne marche pas.Comment faire ?Merci d'avance

View 14 Replies View Related

Sql Prob

Oct 26, 2006

hi, i get

Msg 208, Level 16, State 1, Line 1

Invalid object name 'a1'.

i do have a1, what is the problem here?

thanks

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[STP_InvoiceByPono]

AS

BEGIN

SET NOCOUNT ON;

declare @Fieldname varchar(25)

declare @stmt varchar(max)

declare fields cursor for select amounttype from Inv_AmountTypes

set @stmt = 'Select distinct a1.date_, a1.suppliercode, a1.invoiceid, a3.pono'

open fields

fetch next from fields into @fieldname

while @@fetch_status = 0

Begin

set @stmt = @stmt + ',(Select b3.amountfc from

invoice b1 inner join Inv_Detailed b2 on b1.InvoiceID = b2.InvoiceID

inner join Inv_Multiplicity b3 on b2.InvoiceID_Detailed = b3.InvoiceID_Detailed

inner join a1 on a1.InvoiceID = b1.InvoiceID inner join a3 on a3.Pono = b3.Pono

where amounttype = ''' + @fieldname + ''') as ' + @fieldname

fetch next from fields into @fieldname

END

CLOSE Fields

DEALLOCATE Fields

set @stmt = @stmt + ' From Invoice a1 inner join Inv_Detailed a2 on

a1.InvoiceID = a2.InvoiceID inner join Inv_Multiplicity a3 on

a2.InvoiceID_Detailed = a3.InvoiceID_Detailed'

exec(@stmt)

select(@stmt)

END

View 6 Replies View Related

ASP.NET / SQL 2k Membership Prob

Jun 10, 2006

Almost have my custom membershipprovider finsihed, but have ran into a small snag.Scenario:: 2 methods. CreateUser(blah blah) and ValidateUserName(string username). CreateUserWizard control calls CreateUser(blah blah) which in the start of the method calls ValidateUserName(string username). ValidateUserName runs a stored procedure on SQL 2k to check and see if user exists in the db. Returns a bool true or false. If true, then everything works correctly and returns a message displaying that the user exists, please enter a different name.Now the problem. If it returns false, meaning that the username doesnt exist and is available to use, it calls a stored procedure to create the record in the users db. This is where the problem lies. It does NOTHING but sit there waiting. But it never seems to time out. If I comment out the validating. It will add a record successfully.Im wondering if it has something to do with calling 2 stored procedures consecutivly????::CODE TO FOLLOW::

View 2 Replies View Related

Jobs Prob

Aug 24, 2001

I have a couple of jobs set on 1 server. I want these jobs to be transferred, or rather copied to another server. Is there a method by which I can just point these jobs to another server, without having to manually create them again ??

Thanks!

View 1 Replies View Related

Datetime Prob

Jan 17, 2001

I need to get only the Time from the datetime result. I am curently using this:
select right(Convert(varchar(20),getdate(),109),8)

Is there any other built function of SQL that we can use to get this ?

Thanks in advance!
SC

View 3 Replies View Related

Replication Prob.

Jun 26, 1998

I am having a problem replicating between 2 servers using 1 distributor. I have server X and server Y.
Server X is a publisher, distributor, and supposedly a subscriber, while server Y is a subscriber and
supposed publisher (using Server X as a remote distributor). My logreader task (off server X since
it is the distributor) gives me the error message "Unable to connect to server Y". I was pretty sure it
is possible to publish to the same server that is your distributor am I wrong? If I am not wrong and
this scenario is possible any ideas on why it isn`t working and how to get it working?

Thanks for the help in advance

Levi Akers

View 1 Replies View Related

Weird Prob!!!HELP

Aug 1, 2002

Whenever I am creating a new database, I am not getting any system stored procedures created :o( the system tables & views are created though :o(
what maybe the problem?

thanks!

View 4 Replies View Related

DTS Wizard Prob...

Aug 12, 2005

Hi,

Having a problem with DTS Wizard crashing in XP, has anyone experienced similiar problems?

Using XP with SP1, transfering excel file to an oracle serve.

View 2 Replies View Related

Another Simple Prob I Cant Fig Out

Oct 21, 2004

I have a table w/ 17K line items. There are only 8.5k I want because each one is duplicated exactly twice. How do I get one of each of these line Items into a new Table?

I tried this:

Select DISTINCT * from DuplicateTable INTO NewTable

And it doesnt work. I am still getting the 17K transported over... any solutions?

View 1 Replies View Related

Web Assistant Prob

Jan 28, 2004

Hi,

i have a few web jobs, that were set up by the DBA that worked here before I did and they have just stopped running and I have no idea why.

The error I get is:

Executed as user: dbo. SQL Web Assistant: Could not establish a local connection to SQL Server. [SQLSTATE 42000] (Error 16804)

Does anyone have any experience with this?

Thanks in advance

View 6 Replies View Related

DTS Package Prob

May 11, 2004

I wrote next ActiveX script. But it doesn't work. Can somebody tell me what i'm doing wrong. It should retrieve the mail and Assoc-NT-Account, but so far i only get errors?

Another question: should I explicitly write this to a table? If yes, how?

************************************************** ******************** ' Visual Basic ActiveX Script '************************************************* **********************

Function Main() Main = DTSTaskExecResult_Success

strServerName = "**********" set oConn = CreateObject("ADODB.Connection") set oCommand = CreateObject("ADODB.Command") set oRS = CreateObject("ADODB.RecordSet")

oConn.Provider("ADsDSOObject") on error resume next oConn.Properties("User ID") = "*********" oConn.Properties("User Name") = "*******" oConn.Properties("Password")="*********" oConn.Properties("Encrypt Password")= True oConn.Open "Ads Provider"

set oCommand.ActiveConnectection = oConn

strQuery =" Select Assoc-NT-Account, mail from LDAP://servername/o=orgName/ou=OrgUnit where objectClass = 'person' order by cn"

oCommand.CommandText = strQuery oCommand.Properties("Page Size") = 99

set oRS.sort ="cn"

i = 0 While not oRS.eof vObjectClass=oRS.Fields("objectClass") bShow = oRS.Fields("mail") > " " if bShow then oRS.Fields("mail") oRS.Fields("Assoc-NT-Account") End If oRS.MoveNext i=i+1 wend End Function

View 7 Replies View Related

Column Prob

Jun 14, 2007

repairJob(repairJobID, description)
->('overhaul','blablabla...')
->('gearbox','blablabla...')

sparePart(sparePartID, description ,cost)
->('sp1', 'oil+bolt+...', 100)
->('sp2', 'bolt, clamp...', 200)
->('sp3', 'protector, cover..', 500)

repair(repairID, repairJob, sparePartID, cost, workShopID)
->('r001', 'overhaul', 'sp1', 100, 'w001')
->('r002', 'overhaul', 'sp2', 200, 'w001')
->('r003', 'gearbox', 'sp3', 500, 'w002')
->('r004', 'gearbox', 'sp2', 200, 'w002')

output:


Is it possible to make it?

View 10 Replies View Related

Quote Prob

Mar 25, 2008

I have a stored proc that creates a view so I can pass parameters. I need to replace the 120 with the variable @MEA. I can't get my quotes right to make this work.
ALTER PROCEDURE dbo.spi_CallList
(@strAgent nchar(4), @MEA int)

AS

DECLARE @Msg varchar(255)
declare @SQL varchar(4000)

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1')
DROP VIEW qs_CallList1

SET @SQL = 'Create view qs_CallList1 as
SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns
FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat))
LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID
WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''')
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null)
AND ((tsd_Claim.clfromdos)<GetDate()-120))
GROUP BY tsd_Claim.clinsnum
ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC'
EXECUTE(@sql)

View 3 Replies View Related

SQL Delete Prob.

Jul 23, 2005

Himy prob is like this..-----------------------------create table ax(i int ,j int)create table ay(i int ,j int)insert into ax values(1,100)insert into ax values(1,101)insert into ax values(2,103)insert into ay values(1,200)insert into ay values(1,201)insert into ay values(1,202)insert into ay values(2,203)insert into ay values(2,204)insert into ay values(2,205)select * from axselect * from ay--------------------I want to delete2 records(count of ax.i = 1) from ay.i = 1 and1 record(count of ax.i = 2) from ay.i = 2expected result :select * from ayi , j-----1 , any data2 , any data2 , any datanote: j is the temporary column in both table.thanksdishan

View 2 Replies View Related

Converting Prob

Dec 4, 2007

Hi Friedns,

I am transfering data from oledb source to excel destination i am getting this error


error : First name cannot be converted unicode datatype to non-unique code datatype

any body plz help me


Thx
subu

View 10 Replies View Related

Small Prob

Aug 17, 2007

i need to concatenate this two database fildes

PATNT_REFNO_NHS_IDENTIFIER defined as varchar
PATNT_REFNO defined as numeric

out put of these tewo colomns like

PATNT_REFNO_NHS_IDENTIFIER = NPA0123
PATNT_REFNO = 0125487


so i need to get a result like
NPA01230125487

any idea

regards
Niranga

View 1 Replies View Related

Prob In SSRS

Mar 10, 2008



Hi All,

My question is i had a report with drop down list which contains nearly 200 items.when i select all items it's shows the error. I am able to get the result up to 19 items.



even i tried to filter at report leval using IN operator it is not working.can any one give some suggation.


My expression is




Fields!DefectText IN " ( ' " & JOIN(PARAMETRS!DefectText.Value , " ' , ' ") & " ' )"



REGARDS
Sith

View 4 Replies View Related

Prob In Installing

Nov 10, 2007

TITLE: Microsoft SQL Server 2005 Setup
------------------------------

SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff

------------------------------
BUTTONS:

&Retry
Cancel
------------------------------

what em i gona do

View 1 Replies View Related

INsert Prob

May 27, 2008

Hi Friends,

I have one application with this some data inserting into MS SQL SERVER some big amount of data insert at this time

lote of data is inserting but some data is not inserting i am not getting any error what i can do could plz help me


some data is not inserting,, i am not getting any error also

View 2 Replies View Related

Export Prob

Oct 16, 2007



Hi Friends,

My Question is,

1)When i export the report the data i am getting in text format.how could i get tha data in number format by default.
2)my report has a total of 15 columns, so when user scrolls ,the first column allways visible.


Waiting for your response

Regards
Sithender.S

View 1 Replies View Related

Store Procedure Prob?

Mar 21, 2007

Hi,I have three different tables. I want one login screen and check all table ,if any user got it,but i have written simple store procedure and i take values by parametrically,how can i know user's table name?ALTER PROCEDURE [dbo].[sp_GirisKontrol]    -- Add the parameters for the stored procedure here    @Username nchar(12),    @Password nchar(12),    @Id int output,    @Rowguid UniqueIdentifier outputASBEGIN    SET NOCOUNT ON;    SELECT @Id=Id,@Rowguid=Rowguid FROM Admin WHERE Username =@Username AND Password =@Password UNION    SELECT @Id=Id,@Rowguid=Rowguid FROM Ogretmen WHERE Username =@Username AND Password =@Password
UNIONSELECT @Id=Id,@Rowguid=Rowguid FROM Ogrenci WHERE Username =@Username AND Password =@Password         if(@Id IS NULL)    SELECT @Id=-1END  

View 3 Replies View Related

Prob With Connection In Sql Server And C#

Feb 23, 2005

Please anybody help me about this.

I have windows 2003 server installed in my pc. I have sql server without any userid and pwd.

so i have written code in c# ,

server=server Address;uid=;pwd=;database=database;

Is the above string works? I have given in code as above as a connectionstring.
I am facing the prob in connection. Please anybody can guide me.

Thanks

View 1 Replies View Related

Prob: SELECT Query

Aug 2, 2000

Hi..,

I am using SQL Server 7.0 SP2.
I've got a table x that has fields say:

id1 id2 a b c d e f

(each field name is separated by a space.
id1 + id2 is the primary key combination for this table)

If there are say 100 records in this table I can write a SELECT query like:

SELECT (a + b + c + d) AS Item
FROM x
WHERE id1 = 'my_value1' AND id2 = 'my_value2'

and I could get up to 16 records (say) using this query. The no. of records I get is variable.

My problem is to produce a single record that contains 16 Item fields. Can anyone advise me how to do this without using a cursor?
I'll be grateful for any help,

Thanks for your patience,

Cheers,

Sudhakar

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved