T-SQL (SS2K8) :: Get Particular Day Between Two Dates?

Aug 8, 2014

I want to list Dates for particular day between two dates.

for eg. if user enters 08/01/2014 and 08/31/2014 with value 0 then all the Sundays and related dates should be display.

if above contamination with 1 then all Mondays,

View 9 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

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]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

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]...

View 7 Replies View Related

T-SQL (SS2K8) :: Display Dates For A Given Value?

Mar 4, 2015

I'm looking to identify the people who visited an office more than once and the dates that they visited. For example I have a table that looks like the following:

Name |Office Visit Date
-----------------------------
John| 2002-01-23
Mary| 2003-02-04
Fred| 2002-11-02
Jane| 2012-06-05
John| 2003-07-12
Mary| 2004-10-12
Fred| 2011-03-20
John| 2009-02-14

I want to display the following:

Name |Office Visit 1 |Office Visit 2 |Office Visit 3
----------------------------------------------------------------------
John|2002-01-23| 2003-07-12| 2009-02-14
Mary| 2003-02-04| 2004-10-12|
Fred| 2002-11-02| 2011-03-20

Note that Jane does not show up because she has only visited the office once and everybody else has visited the office at least two times.

View 2 Replies View Related

T-SQL (SS2K8) :: How To Get MIN / MAX Dates Per Grouping

Jul 8, 2015

I have a table with Employee, it lists their department, and has a row for each "position/promotion". (Just the pertinent fields below in the example shown of course and only a single employee for example)

An employee may have a single row in a department, or if they were promoted from line staff to manager they could have many.

Additionally, an employee may move around departments during their employment.

I need to get the min and max dates (basically duration) of their time at each department.

The issue is because they repeated departments when I go to group it loses movement between departments.

Raw data:

Employee DepartmentId StartDt EndDt
----------- ------------ ---------- ----------
999 5 2000-01-01 2001-04-30
999 7 2001-05-01 2005-06-30
999 7 2005-07-01 2006-09-30
999 5 2006-10-01 2009-10-31
999 5 2009-11-01 2012-01-01

Result of doing MIN/MAX:

Employee DepartmentId MinStartDt MaxEndDt
----------- ------------ ---------- ----------
999 5 2000-01-01 2012-01-01
999 7 2001-05-01 2006-09-30

However, Ideally I would get 3 rows (Department 5, then the stay at Department 7, then back to Department 5 again).

Employee DepartmentId MinStartDt MaxEndDt
----------- ------------ ---------- ----------
999 5 2000-01-01 2001-04-30
999 7 2001-05-01 2006-09-30
999 5 2006-10-01 2012-01-01

I played with RANK and ROW_NUM hoping I could get it to group / partition on each department and repeat the number for each department so the first department 5 would be RowNum=1, then the next group of Dept 7 would be =2 and the last group of Dept 5 would = 3 and then I could min/max on that grouping, but I didn't have any luck with that approach either.

My table:
CREATE TABLE [dbo].[EmployeeDepartmentHistory](
[Employee] [int] NOT NULL,
[DepartmentId] [int] NOT NULL,
[StartDt] [date] NOT NULL,
[EndDt] [date] NOT NULL
) ON [PRIMARY]

[Code] .....

View 2 Replies View Related

T-SQL (SS2K8) :: Finding Gaps In Dates

Mar 25, 2014

I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:

CREATE TABLE #SessionTest(SessionId int,Logindatetime datetime, Logoutdatetime datetime)

INSERT INTO #SessionTest
SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL
SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL
SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL
SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL
SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL

[code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Retrieve Dates For Last 7 Days

Mar 26, 2014

The following query was used for retrieving dates for the last 7 days . Untill February this query was running fine and would return the last seven days date including today.

SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 7

However from March (not sure of the exact date)..the query below would only give us 7 days until yesterday..i.e it would list dates from 3/19,3/20,3/21,3/22,3/23,3/24,3/25 and not 3/26 ..

I changed the query to <= 6 and it works as expected. But still not sure why it would not return todays date with <= 7.

SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 6

View 5 Replies View Related

T-SQL (SS2K8) :: Get Missing Dates In Table

Aug 18, 2014

I've a request: I've a table with a date column and an if numeric data type.

I've to check from now on 7 days ago if any value is missing in my date field.

So if I've f.i. in my table
ID DAYS
1 2014-08-11
2 2014-08-12
3 2014-08-13
4 2014-08-14
5 2014-08-17

then my output should be:
2014-08-15
2014-08-16
2014-08-18

How to do this?

View 1 Replies View Related

T-SQL (SS2K8) :: Get Earliest Date From Two Dates

Dec 10, 2014

I have two dates. How do I get the one that is the lowest. One may be null. I don't want null unless they are both null

I tried..

DECLARE @Handle date
SELECT @Handle = dbo.getTrkLeastDate('2014-12-09',NULL)
print @Handle
ALTER FUNCTION [dbo].[getTrkLeastDate] (@d1 date, @d2 date)
RETURNS datetime

[Code] .....

View 4 Replies View Related

T-SQL (SS2K8) :: Current Year Begin And End Dates

May 6, 2014

I am working on some payroll related code which currently has the following hard-coded CASE statement (I won't include the entire thing, it is lengthy):

AND b.TCDateTime BETWEEN '2013-01-01 0:00' and '2013-12-31 23:59'

I want to get rid of the hard coding, and have this use current year dates. So for 2014, it should reference rows where the TCDateTime is >='2014-01-01 0:00' AND <'2015-01-01 0:00'..I think the following would accomplish this, but would like confirmation that this is the 'best' way (and that it will work!)

SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy, GETDATE())) + '0101') AS curryrbegin
--output should be yyyy-01-01 00:00:00.0 where yyyy is current year
SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy + 1, GETDATE()))
+ '0101') AS nextyrbegin
--output s/b nextyear-01-01-00:00:00.0

Then, change the CASE statement to read:

AND (b.TCDateTime >= curryrbegin AND < nextyrbegin)

View 8 Replies View Related

T-SQL (SS2K8) :: How To Find Saturday Occurrence Between Two Dates

Jun 17, 2014

I'm trying to find if the Saturdays worked in Bi-Weekly period is first or second, based on the Saturday occurrence the CODEID column value changes from 01 to another #, for example if it's first Saturday then CODEID changes from 01 to 02 and it's second Saturday then the CODEID changes from 01 to 03.

Below is my table, current result and desired results.

My Table:

WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT * FROM SampleData

Current Results

PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0001Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0001Saturday05/10/2014

Expected Results

PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0002Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0003Saturday05/10/2014

View 9 Replies View Related

T-SQL (SS2K8) :: How Datediff Determine Difference Between Two Dates

Jan 19, 2015

select datediff(wk, '2015-01-11', '2015-01-19') returns 1 (shouldn't it return 2?)
select datediff(wk, '2015-01-10', '2015-01-19') returns 2

View 5 Replies View Related

T-SQL (SS2K8) :: Average Of Time Between Multiple Dates

Oct 7, 2015

I have a dataset as such:

Student TestTypeDate TestCnt
111-22-1111English2015-09-01 10:00:00 1
111-22-1111Math2015-09-02 11:00:00 2
111-22-1111Geo2015-09-03 12:00:00 3
222-11-2222English2015-09-01 10:00:00 1
333-22-1111English2015-09-01 10:00:00 1

[Code] ...

So some have just 1 test and some have multiple. I have a count for each. What I need to do is use that count and get an average time between each test per student.

View 9 Replies View Related

T-SQL (SS2K8) :: Calculate And Display Week Between Two Dates

Nov 4, 2015

I want to display week between two dates as below.

requirement is as:

suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016

Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

Say for example the column date is coming as 10/23/2015 then it will fall in week-1

Same way if 11/01/2015 falls in week2.

View 3 Replies View Related

T-SQL (SS2K8) :: Join Or Pivot / Unpivot For Mismatch Dates

Jul 31, 2014

I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I try the SQL script below but it returning 6 rows instead of 3–

select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
,[ineffective_date]

[code]....

View 3 Replies View Related

T-SQL (SS2K8) :: Select Past 3 Dates And Determine If 1st And 3rd Are Within Timeframe?

Sep 7, 2014

I have a table with addresses and activity dates. I need to be able to retrieve the past 3 activity dates and see if the first and last occurred within 15 days. If so, I need to flag them.

Using max date gets me the last date but not the previous two. I was trying to use top 3 in desc order and that didnt seem to work either.

View 9 Replies View Related

T-SQL (SS2K8) :: Create Periods Transaction Dates And Make Them Columns

Sep 4, 2014

I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.

I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!

select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
sum(gl_ledger.amount_n) as Net
from gl_account

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Pass Dates For Previous Months Start And End Date

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

T-SQL (SS2K8) :: Selecting Data By Date For Last Five Days AND Avoiding Weekend Dates

Apr 16, 2014

What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:

DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG
I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment)
,(
((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE

[Code]....

The query works with the following exceptions:

My issues are:

1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.

2. Avoid weekend dates. I need the query to display only weekdays

3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.

View 6 Replies View Related

T-SQL (SS2K8) :: Historical Data Where Number Of Records Exists Between Two Dates With Different Years

Jul 10, 2015

Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.

A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.

SELECT
MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10],
MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10],
MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11],
MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11],
MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],

[Code] ....

I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).

View 4 Replies View Related

T-SQL (SS2K8) :: Get Last Record In A Month When No Guarantee Month Exists Of Unique Dates?

Apr 22, 2015

following table global_usage

ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01

[Code] .....

Now the problem,per month I need the most recent value so I'm expecting

ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01

I've tried a few thing like group,having even row_number() but I keep getting wrong results

View 6 Replies View Related

T-SQL (SS2K8) :: Get Week Numbers From Dates - Saturday Being Start Of Week

Sep 17, 2015

i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday

CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES

[code]....

The output should look like this

weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000

View 4 Replies View Related

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

Mar 2, 2006

SQL 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?

View 3 Replies View Related

Comparing Dates With Today Dates

Jun 21, 2005

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 Related

Generate Dates Between Two Dates

Nov 8, 2006

Hi,

I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.

I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:

SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)

Any advice will be greatly appreciated!

View 1 Replies View Related

Getting Individual Dates Between 2 Dates

Mar 4, 2008

Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?

So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?

View 13 Replies View Related

Leap Year Dates Causing Error When You Pull Dates For Non Leap Year

Feb 28, 2008



I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error


Here is what I'm trying to generate






InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev

ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65


ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
NULL
NULL



Here is the error that I'm getting:



Code Snippet

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(4834 row(s) affected)



Here is my Transact-SQL Syntax (summarized because I Couldn't post it):

SELECT

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'

AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'

THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT Trans1.TR_Amount

FROM dbo.Trans Trans1

WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)

AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)

AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,


--AA Script Here AS AARmRev,

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)

= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT SUM(Trans1.TR_Amount)

FROM dbo.Trans Trans1

WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND

trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))

+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND

trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev



FROM dbo.EntityDef INNER JOIN

dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN

dbo.CurrentDate INNER JOIN

dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate

WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')

ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate


I appreciate all your help

Thanks

View 13 Replies View Related

Sql Dates

Mar 30, 2007

Hello All,
 I am trying to Add certain number of days to a particular date.
and my requirement is that it need to exclude all saturdays and sundays and then give me the resultant date in Sqlserver.
 Please can anyone help me in achieving it.
 thanks
Shiva Kumar

View 2 Replies View Related

Dates

Jan 10, 2002

Hi can anyone help me , or am I on the wrong track.

Is there any easy way to create a stored procedure that inserts into a table the relevant months dates into a table based on the month and year as parameters.
I.e say the parameters passed are 01/2001 hence based on this all of the month of January 2001 dates are inserted into a table in this format : 'Jan 01 2001 12:00:00'

Thanks

View 1 Replies View Related

SQL Dates

Apr 19, 2004

OK, so I can return (& therefore use) the last day of last month using

select (GETDATE()- DATEPART ( dd , GETDATE()))

Which returns 31-03-2004 (dd-mm-yyyy)

Has anyone a good way of determining a way of deriving say a date such as 01-01-2004 (1st Jan)....

I'd like to get some data where my dmd.sdate falls always in the previous quarter.. but would like it to be not hard coded...

Currently I have

SELECT cust.no,
cust.surname,
dmd.csan,
dmd.sdate
FROM pdunity.dbo.cust cust,
pdunity.dbo.dmd dmd
WHERE cust.rowno = dmd.rowno_custdmd_cust
AND(dmd.sdate
BETWEEN '01/01/2004'
AND(GETDATE()- DATEPART ( dd , GETDATE())))
ORDER by dmd.sdate asc

Is there some form of function I can use to return 01/01/2004 automatically ?

Will

View 6 Replies View Related

Dates ...

Nov 21, 2005

why does this sql return the dates in different formats? Is it a default setting on the server or do i just handle it on SQL?

use northwind

select top 20 orderdate from orders

select top 20 left(orderdate,10) from orders


thanks

fatherjack

View 1 Replies View Related

Dates For The UK

Feb 11, 1999

We are deploying a system in the UK in SQL Server 6.5. They want the date to default to dd/mm/yy. Is there a way to set this on a permanent basis? Is there a way to set the server so any time a date is display, including getdate(), the result is dd/mm/yy?

View 2 Replies View Related

Dates Between 2 Other Dates

Aug 16, 2002

Hi All

I have a table which contains apart from other fields, 2 dates. i.e. startdate and enddate.

The startdate is always less than / equal to enddate.

I want to write a stored proc where I will give a date as a parameter and it should give me all the records there my date is between the startdate and the enddate.

Also I want to create a temp table in the procedure and the table gets populated with all the dates that comes between startdate and enddate, both dates inclusive.

I know this might be a tall order, but I am new to writing stored procedure, so I will be obliged if somebody can help me out

Yusuf

View 1 Replies View Related







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