Start Date Question: How To Modify It In SQL Server
Oct 12, 2000
Hi!
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?
Thanks,
Helmut
View 1 Replies
ADVERTISEMENT
Apr 4, 2007
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)
clientid StratDate EndDate
1 2004-01-01 2004-05-01
1 2004-04-01 2004-05-01
1 2005-04-01 2006-01-01
Table create
CREATE TABLE [dbo].[TABLE_TEST] (
[Client_ID] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[STARTDate] [datetime] NULL ,
[ENDDate] [datetime] NULL ,
)
GO
INSERT
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2004-01-01','2004-05-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2004-04-01','2004-05-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2005-04-01','2006-04-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('2','2004-06-01','2004-07-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('3','2004-09-01','2004-010-01')
Go
Thanks for help
Husman
View 1 Replies
View Related
Feb 12, 2015
I have a SSRS report using 2008 R2. It prompts the user for the start and end dates. This all works. But now I want the start date parm to default to the first day of the current month and the end date parm to default to the last day of the current month.In the new query window in SQL Server Management Studio, I can run this chunk of code to get the first day of current month:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
And this code to get the last day of current month:
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),
DATEADD(MONTH, 1, GETDATE()))
But I don't know how to do this in SSRS 2008. How can I make my start / end parms to get these values.
View 5 Replies
View Related
Jun 17, 1999
HOW TO GET A FILE'S CREATE DATE??
From SqlServer I need the create date of a file on C or D.
Is there a function or code to retrieve the create date,
or an API that could read and pass back the file's creation date???
View 1 Replies
View Related
May 24, 2015
I have a table called 'AssetPlacements' that shows the dates when certain objects (AssID) were placed at certain locations (LocID).
ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11
I'd like to show the assets with a start date and end date for the placement of the asset.
The start date to be the placement date and the end date to be the next placement date of the asset.
Where there is no next placement date to then show the end date as the current date, so hopefully the table will show as the following.
ID AssID LocID StartDate EndDate
1112015-05-01 2015-05-06
2122015-05-06 2015-05-09
3132015-05-09 [GetDate()]
4212015-05-03 2015-05-07
5222015-05-07 2015-05-11
6232015-05-11 [GetDate()
I'm guessing some sort of recursion is required here to produce this.
View 7 Replies
View Related
Jun 3, 2015
Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'
[Code] .....
View 8 Replies
View Related
Apr 19, 2015
My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.
Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01
[code]...
In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01
View 10 Replies
View Related
Apr 23, 2015
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
View 4 Replies
View Related
Sep 1, 2006
In my Derived Column Transformation Editor I have something like this:
DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])
where [Schedule] is a database timestamp field from a OLEDB Datasource.
I want to produce a string something like: "DD/MM/YYYY"
using the expression above, I get something really wierd like "1905-07-21 00:00:00"
Help much appreciated!
View 10 Replies
View Related
Jul 23, 2005
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)
View 2 Replies
View Related
Jan 14, 2008
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
View 4 Replies
View Related
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..
Thanks
Karen
View 6 Replies
View Related
Mar 14, 2014
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.
View 9 Replies
View Related
Jun 10, 2015
Need a script to capture current date modify table list(12 AM to 11:59 PM PST) in a database.
View 7 Replies
View Related
Aug 6, 2015
I have a report which queries events occurring during the reporting time frame the user selects.
A user selects arrivalDateStart as 8/1/2015 and arrivalDateEnd as 12/31/2015 and the results are returned.
The issue is that there is an event that began 7/31/2015 and ends 8/2/2015. This does not appear on the report. The user does not want to have to pick an earlier arrival date as the parameter of his report to pick up anything that was already in progress prior to the beginning of the month.
I am trying to figure out how to add an expression that will also return events that are in progress over the arrival date chosen for the report.
View 9 Replies
View Related
Sep 10, 2015
I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
[code]...
View 4 Replies
View Related
Jul 18, 2006
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.
Mahathi.
Mahathi
View 3 Replies
View Related
Jun 19, 2007
Hello All,
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?
Thanks.
View 4 Replies
View Related
Nov 12, 2014
I'm looking at the following Records:
cust_no item_no start_dt end_dt price
1060 2931 2011-02-06 9999-12-31 1.23
1060 2931 2011-04-18 9999-12-31 2.00
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.
View 3 Replies
View Related
Apr 16, 2015
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."
View 5 Replies
View Related
Aug 15, 2006
I need start and end date of previous month, such as StartDate=07/01/2006 and EndDate=07/31/2006 for currentdate 08/156/2006
How can I do this?
View 1 Replies
View Related
Oct 28, 2004
Hello,
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??
Thanks in advance!!
View 4 Replies
View Related
Feb 17, 2006
Can anyone explain the result of the sql statement
Select DATEADD(wk, DATEDIFF(wk, 6,getdate()), 6)
Can we use this to get the start date of the current week always???
View 2 Replies
View Related
Aug 15, 2006
I need start and end date of previous month, such as StartDate=07/01/2006 and EndDate=07/31/2006 for currentdate 08/156/2006
How can I do this?
View 6 Replies
View Related
Jan 16, 2008
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])
View 2 Replies
View Related
Apr 17, 2014
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')
[code]....
Expected Output
---------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype-
'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'
'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'
'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'
'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4'
View 9 Replies
View Related
Oct 9, 2015
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
View 6 Replies
View Related
Jul 23, 2005
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!
View 5 Replies
View Related
Aug 16, 2006
Hi,
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).
Regards,
Simon.
View 7 Replies
View Related
Sep 27, 2005
Greetings....
View 4 Replies
View Related
Oct 15, 2014
Any UDF that accepts a Month and Year and returns Start Date and End Date.
For example @Month = 01 and @Year = 2014 would return a StartDate of 2014-01-01 and an EndDate of 2014-01-31.
View 8 Replies
View Related
Mar 16, 2015
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
View 1 Replies
View Related
Jul 7, 2015
I have a detailed report in ssrs in which data can come from start date and end date parameters.
but the problem is. for example i gave startdate as 01/01/2015 and end date as 09/31/2015 then the data must be in displayed in such a way that jan month in one tab and feb month data in one tab and sooo on to sep month data in new tab when i export to Excel.
Is this possible in ssrs ?
View 1 Replies
View Related