How To Handle A Start Date And End Date.. In A Database..
May 6, 2008
hi,
I am getting a data in a dbf file and they have a StartDate and end Date for where the Statments are valid for.. How can i incorporate them into the database..
right now We are doing with PeriodId.. like while the user imports the data they select which period they want to import the data into.. But now if i have to get those details from the Database how do i store it in the Database..and i need to store them in multiple tables..
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID StartDate 1 2014-01-01 1 2014-02-01 1 2014-05-01-------After Gap Restarted 1 2014-06-01 1 2014-09-01---------After last gap restarted 1 2014-10-01 1 2014-11-01 2 2014-01-01 2 2014-02-01 2 2014-03-01 2 2014-04-01 2 2014-05-01 2 2014-06-01 2 2014-07-01
For Id 1 the start date after the latest gap is 2014-10-01 and for id=2 there is no gap so i need the minimum date 2014-01-01
My Expected Output id Startdate 1 2014-10-01 2 2014-01-01
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
need help help me -CURSOR backward insert from End Date > to Start Date how to insert dates from end to start like this SELECT 111111,1,CONVERT(DATETIME, '17/03/2008', 103), CONVERT(DATETIME, '01/03/2008' i explain i have stord prosege that create mod cycle shift pattern and it working ok now i need to overturned the insert so the first insert is the '17/03/2008' to '16/03/2008' ..15...14..13..12...2...1 so the first insert be '17/03/2008' next '16/03/2008' ...........................01/03/2008
tnx
Code Block DECLARE @shifts_pattern TABLE ([PatternId] [int] IDENTITY(1,1 ) NOT NULL, [patternShiftValue] [int]NOT NULL) declare @I int set @i=0 while @i < 5 BEGIN INSERT INTO @shifts_pattern ([patternShiftValue] ) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 set @i=@i+1 end declare @empList TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) INSERT INTO @empList ([empID], [ShiftType],[StartDate],[EndDate]) SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '17/01/2008', 103) -- create shifts table declare @empShifts TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL ,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL) DECLARE @StartDate datetime DECLARE @EndDate datetime Declare @current datetime DEclare @last_shift_id int Declare @input_empID int ----------------- open list table for emp with curser DECLARE List_of_emp CURSOR FOR SELECT emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp OPEN List_of_emp FETCH List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate SET @current = @StartDate ----------------- -- loop on all emp in the list while @@Fetch_Status = 0 begin -- loop to insert info of emp shifts while @current<=@EndDate begin INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate]) select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate from @shifts_pattern as shift where PatternId=@last_shift_id+1 -- if it is Friday and we are on one of the first shift we don't move to next shift type . if (DATENAME(dw ,@current) = 'Friday' ) and EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3)) -- do nothing --set @last_shift_id=@last_shift_id print ('friday first shift') ELSE set @last_shift_id=@last_shift_id+ 1 set @current=DATEADD( d,1, @current) end FETCH List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate -- init of start date for the next emp set @current = @StartDate end CLOSE List_of_emp DEALLOCATE List_of_emp select empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift RETURN
Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2.
Hi, In my Excel file, The Application date column contains empty for some rows. In SSIS I am using one Data Conversion to that Application Date column to change it as Date[dt_Date]. This data conversion is giving error Conversion failed. In Sqlserver table, I declare ApplicationDate column datatype as DateTime. I want to keep those empty date values as Null in Sqlserver. I tried the IMEX=1 property still it is not working. How to solve this error?
We have a table in our legacy database system representing health insurance polices. The customer can, and usually does, renew the policy after 12 months. The legacy database uses the renewal string "99/99/9999" to signify "continuous until cancelled", in other words, "forever", or until cancelled
We need to convert this legacy table into a sql 2005 table, which supports the concept of "forever". But how can we do this? ("99/99/999" is not a valid sql date type and we don't want to use varchar for dates.)
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
Hi all I have an application which takes start time from a different date. My actual requirement is "How to take start time someday in august or other month if the current day is in the month". I will be thankful to any help provided.
In my report I need two parameters, StartDate and EndDate to display a certain period of sales for example. How can I enforce the fact that once the user picks a StartDate, the EndDate cannot be prior to StartDate and vice versa?
would someone be able to shed some light as to why the date returned to my windows application does not match the date that is actually stored in the database. the products_soldDate field is assigned 1/1/1900 at the product records creation time and is updated when it's sold. when the windows application received that data and I check for it I get the following value 12/31/1899 11:00:00 PManyone has a clue as to what is happening here. It worked fine when I hosted it on a MS SQL 2000 database I just recently moved the data over to a SQL 2005 database
The dB dates are store in db as 20140303 when I bring this to my application to pick a date, the apps shows the date as 20140303 how can I change this to show in the format 03/03/2014 in my application?
I want to be able to pull the records with the earliest date 2011-02-06 ...
There were other records with this same customer and item number. I used this script to return the two above.
select * from price where end_dt > getdate()
Now I need to add something so it only returns the record with the earliest date. I'm going to run this on a table that has many customer and item combinations.
I have a set of MS SQL reports, that need to always run on a certain day of the month. Generally the 20th. If the report was to run few days before the 20th, say on the 10th, I wish to retrieve those days between the 20th from the previous month, till the current date.
e.g: '2015-4-10' should only return 20 days worth of data.
I have tried the following query:
SELECT DATEADD(D, 1, MAX(CAST(DateTimeStamp AS DATE))) As EndDate, MIN(CAST(DATEFROMPARTS(DATEPART(YEAR, DateTimeStamp),DATEPART(MONTH, (SELECT CASE WHEN DATEDIFF(DAY,DATEPART(DAY, GETDATE()),28) <0 THEN (SELECT DATEPART(MONTH, GETDATE())) ELSE (SELECT DATEPART(MONTH, GETDATE()) -1) END AS Date)),28)AS DATE)) AS StartOfMonth
FROM tbLogTimeValues WHERE DATEPART(YEAR, DateTimeStamp) = DATEPART(YEAR, DATEADD(M, -1, GETDATE()))
Which parses ok and managed to test all individual queries, however, as a whole, I get the following error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:
Customer StartDate EndDate A 4/1/04 4/15/04 A 4/15/04 5/1/04 A 5/1/04 5/15/04 A 5/16/04 5/28/04 A 5/28/04 6/5/04 B 5/1/04 5/15/04 B 5/16/04 5/20/04
The results I am looking for would be as follows:
Customer A : Outstanding 4/1/04 - 5/15/04 Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string ) Customer B : OUtstanding 5/1/04 - 5/15/04 Customer B : Outstanding 5/16/04 - 5/20/04
I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??
how to generate date backward from end to start like this begin -- loop to insert date backward while @end_date>=@start_Date begin INSERT INTO @tb_temp
from middle of the month to end of the month
serial date ------------------------------ 1 19/03/2008 2 18/03/2008 3 17/03/2007 .............. 19 01/03/2007
and put it in this part of code tnx
Code BlockDECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
SET @current = @StartDate
-----------------
-- loop on all in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
I got a DATE question here... In Oracle, all dates are based upon a fixed point in time. So internally a specific date is stored as a REAL number (which is something like the number of seconds from that specific date onwards).
How can I change that start date upon which all dates in Oracle are based?
SQL Server 7 has something similar. How can I change the start date for SQL Server?
I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .
Create Table Test (Tsq INT IDENTITY (1,1), Data Varchar (150), ts datetime, Tpkt_type int) insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')
I'm new to sql. Can someone help me to write a script to select overlapping start dates for each client records. For example: Clientid 1 have 3 episode as below(I only want to see the first two records with overlapping start date records)
I have weekly billing cycle. And the billing cycle can start on any day of the week.
Assumption: Start day of week is MONDAY
Hence, the following
DayNumber Day 1 Monday if start day is 1, then billing cycle is Monday - Sunday 2 Tuesday if start day is 2, then billing cycle is Tuesday - Monday 3 Wednesday if start day is 3, then billing cycle is Wednesday - Tuesday 4 Thursday if start day is 4, then billing cycle is Thursday - Wednesday 5 Friday if start day is 5, then billing cycle is Friday - Thursday 6 Saturday if start day is 6, then billing cycle is Saturday - Friday 7 Sunday if start day is 7, then billing cycle is Sunday - Saturday
For a given date, i need find start date and end date of current billing cycle.
For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015
I have a transaction log that tracks issues from a call center. Eachtime an issue is assigned to someone else, closed, etc. I get a timestamp. I have these time stamps for the beginning of an issue to theend of an issue and I'd like to determine how many business hours theseissues were open.Issue BeginDt Enddt Total hours1 3/29/05 5:00 PM 4/1/05 2:00 PM 69Basically, this is the type of data I'm looking at and my hours of workare from 7:30 - 5:00 weekdays. I need to come up with a way to removeall nonbusiness hours, weekends, & holidays from the difference of thetwo dates. Issues can span for 2-3 days or 20-30 days.Please let me know if anyone has any ideas or has done something likethis before.Thanks!
I use MS certificate server to request/make server certs but the "not before", or start date is tomorrow for a 1 year cert. I dont care how long but I want the cert to start immediately (today).
Hi all can you help me please, I need the users to be able to enter in a date for a self generating report. Usually I use the @Enter_Date but I need them to be able to enter a start date and a end date But when I run this query SQL gives me an error message Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'Enter the Start Date'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'Enter the End Date'.
What should use in place of BETWEEN [Enter the Start Date] And [Enter the End Date]??
SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes FROM [Main Table] WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date])) ORDER BY [Main Table].[IR Number]
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1