Transact SQL :: Get Date Of All Weekends Of The Year Without CTE

Sep 1, 2015

I want go get all weekends of the year (year dynamic) with out CTE concept, because I need to implement in 2005 version.I have googled but getting only CTE examples.

Query to get the weekends in a year.

View 3 Replies


ADVERTISEMENT

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

Oct 14, 2015

Here I have 2 Dates. CreatedDttm & ModifiedDttm.

I want  - DATEDIFF(Day,CreatedDttm,ModifiedDttm)  and I have to exclude the Weekend days from that query result.

View 10 Replies View Related

Transact SQL :: How To Get Month And Year For A Given Date

Jun 3, 2015

I have a query for which in the where clause i use where Year(openDate) = Year(GETDATE()) and Month(OpenDate) = Month(GETDATE())-1 which would give me the data i needed for this year last month. However if i run this query on Jan 2015 or Jan 2016, this query would fail.

how to modify my where clause so that it runs regardless of even if its Jan ?

View 6 Replies View Related

Transact SQL :: How To Concatenate Year / Month And Day Into Actual Date

Jan 10, 2012

I am working with a SQL Server database that was set up to store the year, month, and day in separate columns, rather than use a single column for the date. I plan to change this so that we store dates and times using the datetime data type. Until then, for now, I need to write transact-SQL select statements to concatenate the year, month and day to create a date that can be displayed in the results window in the format yyyy/mm/dd.

I can't seem to find any built-in function in SQL Server that will let me do this. Of course, using Excel or Access, I can use the DATE() function to reconstruct the date into yyyy/mm/dd. Is there a way to reconstruct the date into yyyy/mm/dd in SQL Server? 

View 7 Replies View Related

Transact SQL :: Function To Find Last Date Of Month One Year Ago - RETURNS ERROR

Apr 28, 2015

I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example,  it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:

Operand type clash: date is incompatible with int

Why is this error being returned?

Here is my function:

CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
       RETURNS date
AS
BEGIN;
  DECLARE @Result date;
  SET @Result =  convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
    RETURN @Result;
END;

View 11 Replies View Related

Transact SQL :: Calculate Working Days Excluding Weekends

Jun 7, 2015

Iam trying to calculate the number of working days between two dates. Iam getting the uouput as only 1 02 r working days??

select  building_number as SchoolID,building_name as Campus,   count( distinct( CASE  WHEN(( DATEPART(dw, CurDate) + @@DATEFIRST)%7 NOT IN (0,1)) tHEN 1 ELSE 0 END)) as NumberofDaysServed   from   Sales sl join Buildings b on  sl.Building_Num =b.Building_number join students2 s on  s.Student_Number= sl.Student_Num   join Sale_Items SI on   si.UID = sl.UID   where  CONVERT(CHAR(10),CurDate,120) between '2015-05-01' and   '2015-05-07'      and VoidReview <> 'v' and  SI.INum = '1'    group by  building_number,building_name order by building_number,Building_Name;

View 8 Replies View Related

Date Difference Measurement And Weekends / Holidays

Jun 4, 2008

The below code works fine to measure the difference in days between two dates.
However, there is an additional business requirement to subtract week-ends, and holidays, from the equation. 
Any ideas on how to accomplish this task, and leverage the below, existing code?  Thanks in advance! 
(SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "

View 2 Replies View Related

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

Aug 30, 2004

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View 4 Replies View Related

How To Do A Year-to-date SQL Query Where Year Commences In August?

Jul 20, 2005

Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel

View 2 Replies View Related

SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays

Feb 2, 2014

I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.

CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours) between two dates.

[Code] ......

View 4 Replies View Related

Fiscal Year Date Help??? I Need The First Day Of The Year To Be 01/27.

Feb 4, 2008


Greetings All,
I have a SQL question that maybe someone out there can help me with. Our fiscal year starts on 01/27. I want to write a query that I can pass a date to and it will return the week number (DATEPART("ww", someDate)) of the year using the Fiscal Year Start Date as the base. Datepart works great except it figures the first day of the year as 1/1. Does anyone know how I can make it work with a first day of the year equal to the fiscal year date 01/27. Any help would be appreciated.
TIA,
~ck

View 2 Replies View Related

Extract Date,month, Year From The Date Getting From Sql Table

Apr 14, 2008

Hello All,
i have three textboxes in a page and i want fill those textboxes  with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne

View 3 Replies View Related

SQL Server 2008 :: How To Get A Year Ago Date From The Particular Date

May 15, 2015

I have a list of students who have taken a test on different dates.

I need a function to calculate year ago date from the date of test.

eg Sam took the test on feb 23 2015

I need to get a feb 23 2014 to see what subjects he studied from feb 23 2014 to feb 23 2015.

How can I do that.

View 3 Replies View Related

Group By Month And Year Transact SQL

Feb 13, 2001

I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.

View 1 Replies View Related

Transact SQL :: First Of November Previous Year

Oct 9, 2015

How I can setup the first of November of the previous year dynamically? So running now it should be 01-11-2014 but if I run in 2th of January 2016 it should be 01-11-2015.

View 17 Replies View Related

Transact SQL :: Query To Get Last Available Data Given Month And Year?

Jul 21, 2015

I am in seach of a query where in I can provide month, year and client name and fetch last available comments from the table.

Client,Month,Year and Comments are columns in that table.

For Ex: If i pass client as A, month as 7 and year as 2015, I should get comments for client A, month July and year 2015 if available.

If data not available, it must go to June month and so on until it finds comments.Also when month is Jan, if query is going back, year also should get changed.

View 10 Replies View Related

Transact SQL :: Setting Up Period One Year Back

Oct 5, 2015

how can I dynamically set up the period between now and one year before in a query like this:

where cast(LEFT(period_start,8)as DATE)>='2014-10-01'
and cast(LEFT(period_end,8)as DATE)<='2015-09-30' and
 
Practically in the second or third of every month I need to run the period late of one year.

View 6 Replies View Related

Transact SQL :: Calculate Rolling Year Sales?

Aug 19, 2015

I have 2 tables as follows:

table A-NewParts
PartNumber
Description
LaunchDate
a123
product a
4/1/2009

[code].....

I want to get end result as sales for each new product for a rolling period from Launch date as year1 sales,year2 sales...and so on which I got through case statements.I am stuck on how to get the total Sales for all products (including new products) in the same rolling period based on the different launch dates for each new product.

PartNumber
Description
LaunchDate
Yr1 Sales
Total Yr1 Sales
Yr2 Sales
Total Yr2 Sales
a123

[code].....

View 3 Replies View Related

Transact SQL :: Display All Days Of A Given Month And Year

Oct 17, 2015

I need a simple query to display all the days of a given month and year

View 2 Replies View Related

Transact SQL :: Get Rows Not Matching Prior Year

Apr 25, 2015

We were asked to fix a query to get rows from a prior year history table that did not match to rows in the current year to show a variance from one year to the next. Rows must match on [corpnbr],[plincd],[pgrpcd] and [pitmcd].  If the combination has rows in the current and prior year ([hstyr]) then everything is fine. However, if they have rows in the prior year (e.g. [hstyr]='2014') but not in the current year (e.g. [hstyr]='2015') then they do not show in the result.  Below is how they designed the table and below that is the stored procedure to pull the records. 

CREATE TABLE [dbo].[BillingHistory](
[BillingHistoryID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[plincd] [varchar](3) NULL,
[pgrpcd] [varchar](4) NULL,
[pitmcd] [varchar](4) NULL,
[newplincd] [varchar](2) NULL,

[Code] ....

View 13 Replies View Related

Transact SQL :: How To Generate Week Ranges For A Year In 2005

Mar 22, 2011

I have to insert YEAR   WEEKNUMBER   STARTDATE   ENDDATE values to a datatable (sayweekrange), if I pass 2011 as year.

Week range starts at 2011-03-28 to 2011-04-03 (because in my database 2010 last week range ends with 2011-03-27) like this I have to generate for 52 weeks.

I want to write a stored procedure, that takes only year as parameter. with this year I have to generate week ranges and insert into my table as shown above.

How can I do this ?

View 8 Replies View Related

Transact SQL :: Total Number Of Days Between Two Dates By Year?

May 6, 2015

I am currently working on a T-Sql query(Sql server 2008) to calculate total no of days between date ranges by year

Table:

Start Date End Date
01/01/2013 04/30/2014
11/01/2014 05/31/2015
06/01/2015 12/31/2015

My expected result.

2013 - 365
2014 - 181
2015 - 365

Note:

Date range can span b/w  multiple years

Date ranges will not overlap

I just want the total number of days covered by the range for each year.

Is there any simple way to do this calculation.

View 9 Replies View Related

Transact SQL :: Compare Event Time Value With Current Year And Month

Aug 27, 2015

I have the following code block

CREATE TABLE #tbl_1 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_1 VALUES ('2015-08-27 13:47:24.123','150','abc')
INSERT INTO #tbl_1 VALUES ('2015-09-27 13:47:24.123','149','acb')
INSERT INTO #tbl_1 VALUES ('2015-10-27 13:47:24.123','148','cba')
CREATE TABLE #tbl_2 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_2
SELECT * FROM #tbl_1 where ? SELECT * FROM #tbl_2

My requirement is to insert values into #tbl2 that are in current month which are event_time values '2015-08-27'

View 4 Replies View Related

Transact SQL :: Select All Days In Week Number X Including Last Year If Necessary

May 24, 2015

SQL express 2012. I am trying to case in the where part and having a syntax errors - This is what i am trying to do:

select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)

declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime

[Code] ....

View 2 Replies View Related

Transact SQL :: Create Columns For Year / Month And Day From Epoch Column

Jul 21, 2015

I'm wanting to create reports in SSDT 2012 which is connected to a 2008R2 database.  I want to have parameters on for my reports where a user is able to select a year such as 2014.  Unfortunate my table containing the data has two columns with a date value.  the first is of the int type and contains an epoch formatted date.  The second is a varchar type and shows the date as 2015-07-01 08:00:00.  I would like to be able to write a query to return the year, monthnumber and daynumber from either of these columns. 

View 5 Replies View Related

Transact SQL :: Select All The Days In Week Number X Including Last Year If Necessary

May 24, 2015

SQL express 2012

I am trying to case in the where part and having a syntax errors - this is what i am trying to do:

Select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)

declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime

[Code] ...

View 2 Replies View Related

Transact SQL :: Pull Records For Current And Previous Calendar Year

Jun 16, 2015

I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?

select id, list_date

from tableA

where list_date > DATEADD(year,-1,GETDATE())

View 5 Replies View Related

Date Need Back Off Four Year

Aug 10, 2004

I have a table in sql server, i need to import this table to another
database in same sql server using DTS, In the table, we have a field called
'qualDate', I need to import the record that the qualDate is in the date of
today and back off four years, for example, today is 8/10/2004, back off four
year should be 8/10/2000, so i need only the record that qualDate is between
8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it
should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.

View 4 Replies View Related

Date Is Less Than Year 2008

May 22, 2008

I have a Actual_dt column that is a type of date. I am trying to get all the values from the table that have the date less than 2008/01/01.

Please help.
Thanks

View 1 Replies View Related

Year To Date Calculator

Jul 27, 2006

Does anyone know a script that will give the year to date? I'm doing a sales report and need to pull year to date information.

View 1 Replies View Related

See If Date Is Same Year And Month And Day

Mar 8, 2008

Hi,

I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.

SO I need to do:

IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)

But it has to be comparing the year,month and day (excluding the time part of the datetime type).

View 9 Replies View Related

Select Max Date Row For Each Year

Feb 28, 2008

Hi !

I've got a list like this one:










uniqueID
technicianID
StatusID
Date

1
1133
2
01-01-2003

2
1133
2
01-03-2003

3
1133
2
01-01-2004

4
1133
3
01-08-2004

5
1133
3
01-02-2007

6
1133
4
01-04-2007


i need to select only the last rows of each year :





2
1133
2
01-03-2003




4
1133
3
01-08-2004




6
1133
4
01-04-2007

How can i achieve that , i have tried with max, but it does not retrieve all the fields.

thanks


View 7 Replies View Related

Want To Concatenate Year And Month To Get A Date

May 14, 2004

Hi,

My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

I'll appreciate your help.

YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....


Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

View 5 Replies View Related







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