hello this is my store procedure and i want to set the hour of the first date to be 00:00:00 and the hour of the second date to be 23:59:59 but i don't know how to do this,so please HELP me
CREATE PROCEDURE dbo.Proba
@date1 smalldatetime ,
@date2 smalldatetime,
@s float output
AS
Select @s=sum(Pay) from RKO where Dates between @date1 AND @date2
GO
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.
CREATE function [dbo].[fnc_myHinkley_ASSY_CalcWorkingMinutes] (@StartDate datetime, @EndDate datetime)
RETURNS decimal(14,2)
/*
Programmer: Goran Borojevic
Date: 5/14/2014
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM.
*/
AS
BEGIN
--check if one of the dates is null
if @StartDate is null or @EndDate is null
RETURN 0
[code]...
guys - is this a decent query to pull all columns (dateCreate)
that have a timestamp less than five minutes?
i know its simple, but i've never done a date compare with minutes or hours
in sql server
thanks
rik:o
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,getdate()) <=5
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,current_timestamp) <=5
I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies View RelatedI have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
Sorry i think i may have posted this in the incorrect forum before - if i have done it again here can someone tell me where i should post this please, thanks:
Hi,
we are having problems with a server Intel RZeon 3ghz, 3gb ram running 2003 service pack 2 with a 70gb drive and and 400 gb drive all with adequate free space. There are 6 hard disks in total and i assume operating at least RAID 5. We have SQL2000 server with a few standard sized databases and a connection to one other server.
A few months ago the back up of SQL server databases started taking 4- 5 hours when before it took 20 minutes. We had actually lost one of our disks in the RAID array and it before this was spotted by our engineers we reindexed the sql databases and defragged both 70gbC: and D: 400gb drives hoping to correct this slow down. Unfortunately the new disk had not been correctly seated and this was why it was taking 4-5 hours. After fixing the disk the backups took 12 minutes again but then started taking 2-3 hours after a few days.
The reindex/defrag did seem to improve the speed of the backups to 12 minutes (from 20 minutes) when the backup did function correctly (also the sql databases' performance improved). However the backups only take 12 minutes after a server reboot - this can last from only 2, up to 5 backups(days) in a row before a slow down to 2-3 hours and again only a reboot will sort out this problem.
NB this intermittent slowdown only occurred after the disk failure.
We have tried monitoring SQL server and can find no CPU/RAM intensive clashes or long running jobs interferring with the back up. Does anyone know what might be going on here? and if there are any server monitoring tools that may help us discover what is causing this problem ?
thanks for your help
best regards
kevin
Trying to get a report to run daily between certain hours. I can set a start time and an "end date" in Report Manager, but as far as I can tell, I can't say run hourly every day from X to Y. am I missing something?
If this isn't doable from RS, can I just find the associated job in the Agent and change the schedule of that job?
I need to be able to add minutes to a datetime value, which only cover working hours.
I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday
Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)
Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05
I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly.
I have a data file listing open and close hours for the day of the week. I need to format it like
Mon-Thurs 7:00 AM - 5:00 PM
Fri 8:00 AM - 5:00 PM
Sat 8:00 AM - 1:00 PM
Sun Closed
Here is what the data looks like now.
Mon OpenMon ClosedTue OpenTue ClosedWed OpenWed ClosedThu OpenThu ClosedFri OpenFri ClosedSat OpenSat ClosedSun OpenSun Closed
7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PMClosed Closed
7:00 AM6:00 PM7:00 AM6:00 PM7:00 AM7:00 PM7:00 AM6:00 PM7:00 AM6:00 PM8:00 AM5:00 PMClosed
There seems like I would end up 5 lines max?
I am trying to calculate the number of hours a device has been used and I cant find how. I need a query that calculated and does an average of the number of hrs used in a week.
View 5 Replies View RelatedHow do I filter a list of Employees where the Sum of "VALIDATED" hours is less than 80? For example.
Here is the flat table
SELECT EMP_NO, hours, IsValidated, rate_type
FROM Pay_Records
WHERE pay_period_id = 2
Order by EMP_NO
Output will be something like this
12345 | 2 |true |REG
12345 | 15 |false |OVR
12345 | 30 |true |OVER
33334 |2| true |REG
Total Validated hours for the Employee 12345 will be 32 NOT 47. How do I list employees who worked less than 80 validated hours. The hours are validated only when it is true.
I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name)
From msdb.dbo.backupset
where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null
and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also
group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
I have to disable newly implemented database encryption. It's a necessity unfortunately. Can I do this during production hours without much of a hit? I know I have to restart the instance after it's done. Can I expect performance impacts or other issues?
View 1 Replies View RelatedSQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies View RelatedI have an application where there is a form to enter some data to the sql server 2005 database.
when the form is submitted, I also use Date.Now to insert the date the article was submitted. It enters fine. E.g. if I submitted it today, it would display 08/06/2006 (which is 06 August 2006). When getting the records, I use, "dd MMM yyyy" to format the date according to my culture. However, the result I get is 08 June 2006
What might be the problem. I hate working with dates with sql server
Hi,
My ultimate goal is to display records within a date range. I am using dropdown lists populated from the database as start and end dates for the ultimate SQL Statement. The field is a smallDateTime type.
The begining date populates without todays date using the following:
connection.Open();
//Auto fill fields
SqlCommand command = new SqlCommand("Select distinct CONVERT(NVARCHAR(10),insertDate,101) as iDate from surveyOwner order by iDate DESC", connection);
command.ExecuteNonQuery();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
FirstDate.DataTextField = "iDate";
FirstDate.DataSource = reader;
FirstDate.DataBind();
}
connection.Close();
}
It works great except records with todays date are excluded throughout.
I was hoping there was something small I was missing.
Thanks,
Doug
When I query a table specifying a date on a datetime column the query
works if I specify it as a date. If I specify it as a number of days since
1899 it returns nothing
Hello, really need some help here!
I've been inserting dates from an asp page into an sql server db in mm/dd/yyyy format, which is what is accepted using smalldatetime. However, when I retrieve a date from the db and display it on my page, it is in dd/mm/yyyy format. This wouldn't be a problem, but if I want to update details in the same table as the date, but keep the date the same, the dd/mm/yyyy format is not accepted. Anyone have any idea what's going on here? sql server seems to accept dates in one format and output them in another...
I'm converting my Access 2003 DB to SQL Server 2005 Express and I use date range comparisons all the time. I learned that the dates also include a time in SQL Server which to me right now seems is going to be a hassle. I don't need the time at all. There will be many times when the current date will be input into a record. All I want is the date part. My first thought was to extract the date part each time I need to use it, but it would be better if I could come up with a test condition that will take any date/time and give the right results. I read about one that uses the following:
get the any dates between 1/1/2006 and 12/31/2006.
The test conditon would be:
SELECT * WHERE(date >= 1/1/2006 AND date < 1/1/2007)
You add a day to your upper range and use the less than operator. This wouldn't be so bad for static dates, but each time I used a dynamic date I'd have to add one day to the upper range before testing. I also read using any kind of function or the "Like" operator on a date renders the indexes useless.
I guess what I'm looking for is method to get the results similar to using the "BETWEEN" statement where I don't have to manipulate any of the dates prior or during testing.
My thought right now is from a different perspective. Any time I need the current date I'd truncate the time part leaving me with a date and a time part of "00:00:00". My only problem with this is what if in code I accidentally forget to truncate the time. I could see this happen giving how much I need to use the current date and being used to not having to worry about the time part.
Any help with this will be greatly appreciated. I think this is my last major hurdle before I'm convinced everything will convert over nicely to SQL Server.
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?
I have an application that is running fine on my development server but it seems to be crashing when I moved it to the production server. The problem is that the production server reads dates like "5/10/2004" or "12/25/2004" from SQL table and interprets them as "10/31/2004" and "25/17/2004" thus displaying a System.InvalidCastException error page that "10/31/2004" or "25/17/2004" could not be converted to a DATE type.
I really don't know why the system is replacing the actual dates in SQL by the last day of that specific month and even worse, replaced the month of December (12) above with the number 17. As far as I know noone changed any date settings on the server but I really don't know where or what exactly to check besides the regional options in control panel.
Please help.
Hey,I've got an Classic ASP app pulling some records out of a DB, whichworked fine when it was Access. When we upped to SQL Server, all of asudden, when the Recordsets came back, all the dates were empty, eventhough the data was showing up in the DB.About a year ago (only code asp apps once and a while), I rememberseeing this and finding something that just had something to do with theorder in which something was being referenced, but I just can't for thelife of me remember what it was.The Dates are set as datetime, length of 8.Anyone know why this happens and a solution?many thanks,rob
View 1 Replies View RelatedI'm trying to add records to a table that includes datetime value using INSERT query.
No matter what date value I'm giving the query, the value saved is 1/1/1900 00:00 .
Also, I'm interested in saving only the short date, without time. I thought changing the type from datetime to smalldatetime would do the job, but it didn't help.
Hi,I have a employee attendance table, and Im trying to return the dates on which an employee is absent. There are no table entries for an employee who is absent on a particular day. So can anyone please help on how i can return the dates on which an employee is absent. Any help will be greatly appreciated, Thanks,Najla.
View 5 Replies View RelatedHi Guys,I need some help here. I've created a asp.net reception system for internal use. The problem I'm having is this, when I update a record all the dates are saved as 01/01/1900. I've had a look at a couple of threads on various forums and sites but none of them seem to have a solution for my problem... I have an AJAX calenderExtenders on the date text boxes and i have played around with all different date formats, currently on yyyy/MM/dd. I've also tried DateTime.Parse(txtStartDate.text) but doesn't work. I pass all the parameters to a stored procedure on the sql server in the correct order. I've included my code below, I have only included date parameters. SqlConnection conn = null; try { string connString = ConfigurationManager.ConnectionStrings["RCM"].ConnectionString; //Initialise the sqlConnection object conn = new SqlConnection(); conn.ConnectionString = connString; //Getting connection string to the db //Create the command SqlCommand command = new SqlCommand("UpdateClient", conn); command.CommandType = CommandType.StoredProcedure; //Opening connection to the db conn.Open(); //Setting input parameters command.Parameters.AddWithValue("@DOB", txtDOB.Text); command.Parameters.AddWithValue("@StartDate",txtStartDate.Text); //Execute T-SQL command.ExecuteNonQuery(); //Close connection command.Connection.Close(); conn.Close(); } catch (Exception ex) { lblError.Text = "An error has occured: " + ex.Message; }
View 18 Replies View RelatedIs there a way to make a single insert (in a loop) and take system dates and insert them in different languages without making a new loop for each language.
Example
Create table dateLanguage(
monthName nvarchar(20),
monthNameEs nvarchar(20),
monthNameFr nvarchar(20),
MonthNamePt nvarchar(20)
)
So for example
January,
Enero,
Janvier,
janeiro
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
I have created a sample Database for the school project,
After executing the query below, the Date column is supposed to have the dates I have entered before,
However the dates shown are 1900.
Any idea why is this happening?
I appreciate your help.
Thank you.
Query:
Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)
--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)
GO
--sample query
select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable
GO
--result
PatientID PresentCharges LastPaymentDate PresentDate
----------- -------------- ----------------------- -----------------------
913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
(24 row(s) affected)
I have created a sample Database for the school project,
After executing the query below, the Date column is supposed to have the dates I have entered before,
However the dates shown are 1900.
Any idea why is this happening?
I appreciate your help.
Thank you.
Query:
Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)
--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)
GO
--sample query
select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable
GO
--result
PatientID PresentCharges LastPaymentDate PresentDate
----------- -------------- ----------------------- -----------------------
913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
(24 row(s) affected)