SQL Server 2012 :: Add Month To A Date

Jun 3, 2014

I am trying to add month to a date. Here is my code

declare @CollectionDate date='10-28-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all

[Code] ....

It is working fine. But when I am giving date '10-30-2014' it shows me the error

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

I can understand the problem that it is for the month of February. But How do I overcome the situation?

View 3 Replies


ADVERTISEMENT

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

SQL Server 2012 :: Just Get Month And Date Only In A Query Without Year?

Mar 18, 2015

This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?

View 3 Replies View Related

SQL Server 2012 :: Converting Year And Month Varchar To Date

Jan 31, 2014

I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.

Year Month Productname
2013 11 ACB
2013 11 CDE

I would now like to add another column called date and store these Year Month as a date to my existing table

Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01

Is there a way I can do it for all the columns of the existing table ??

View 3 Replies View Related

SQL 2012 :: Generating Date Range Values (start / End Dates) From Month Columns With Boolean Values

Jan 13, 2015

I've got some records like this:

ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0

where each month field has a 0 or 1, depending on if the person was enrolled that month.

I'm being asked to generate a table like this:

ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014

Is there some slam dunk way to do this without a bunch of If/Then statements?

The editor compressed all my space fields, so the column headers are off in some places.

View 8 Replies View Related

First && Last Date Of The Month In SQL SERVER 200

Mar 3, 2004

Hello,

Is there functions which returns the first and last date of a month in SQL server 2000 ?

View 4 Replies View Related

SQL Server 2012 :: Second Last Work Day Of Month

May 1, 2014

I would like to get the second last work day of the month.

View 8 Replies View Related

SQL Server 2012 :: Get Latest Occurrence Of X Month?

Jan 17, 2014

I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.

At the moment I'm using this:

DECLARE @Date DATE = '20131217'
SELECT CONVERT(DATE, CAST(DATEPART(YEAR,
IIF(
--If we're at the start of a year
--we'll need to go back a year
DATEPART(MONTH, @Date) IN (1,2,3),
DATEADD(YEAR, - 1, @Date),
@Date
)) AS VARCHAR(4)) + '0401')

View 8 Replies View Related

SQL Server 2012 :: How To Show Only First Friday Of Every Month

Jul 2, 2015

I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.

View 9 Replies View Related

How To Change Date To Month And Month To Date ?

May 6, 2008



In my database . Some to the dates has been stored wrongly .
for example
03/04/2008 to be stored in database . But in my database it was stored as 04/03/2008 . like this i have more that 100 records . How can i change this to correct format using query . Guide me urgent .

View 8 Replies View Related

SQL Server 2012 :: Calculate Customers Age And Value By Month And Year?

Sep 22, 2014

What's the best way to calculate a customers age and value by month and year?

I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:

select fh.contact_number
, concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year
, cast(fh.transaction_date as date) as transaction_date
, sum(fh.amount) as ttl_amount_in_month

[Code] .....

It seems to work, but any better way to achieve this?

View 1 Replies View Related

SQL Server 2012 :: Query To Get The Month Wise Data?

Sep 13, 2015

I have the data in the below format.

Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1

In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.

Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1

View 6 Replies View Related

SQL Server 2012 :: Comparing Data For A Certain Day Of Week By Month For Every Year

Oct 21, 2014

I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.

View 9 Replies View Related

SQL Server 2012 :: Calculate Total On-peak And Off-values For A Month

Nov 25, 2014

SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.

On a daily basis i store the hourly values of the meter in a SQL table.

On-Peak

Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)

off-Peak

Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak

Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak

Here is the DB Table Structure:

Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)

View 9 Replies View Related

SQL Server 2012 :: Query To Find The Difference In Values From Previous Month?

Dec 12, 2013

I have my sql tables and query as shown below :

CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);

[code]....

I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'

SELECT T.[Year],
T.[Month]

-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)

[code]....

What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.

Something like this :

+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----

View 3 Replies View Related

SQL Server 2012 :: Split Total Days And Amount Between Start And End Month

Mar 21, 2015

I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required

For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.

Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.

As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month

Period No of Void Days Rent Loss
06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57
01/10/2014 - 15/10/2014 15 349/7*15 = 747.85

I have uploaded a screenshot of how the result on this link: [URL] ....

Declare @void Table
(
PropCode VARCHAR(10)
,VoidStartDate date
,LetDate date
,Market_Rent Money

[Code].....

View 4 Replies View Related

SQL Server 2012 :: Find Trend How Employee Makes Sales Every Month?

Jun 7, 2015

How do I find sales trend of an employee via comparing current month and previous month sales?

I got so far query upto following,

;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,

[Code]....

I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

View 3 Replies View Related

SQL Server 2008 :: How To Default Start / End Date Parameters To First / Last Day Of Current Month

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

SQL Server 2012 :: Create View To Allocate Amount Per Month - Financial Year

Mar 21, 2015

I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below

Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.

25,000 June 2015

View 7 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

Date Format - Column Which Select First Day Of Given Month Of Converted Date

Oct 21, 2013

Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,

Also create a new column called “BegMonth” which selects first day of the given month of the converted date column

The table is ;

SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]

Results
FDMSAccountNoINSTALLATION_DATE
87800000088420030521

Required Results
FDMSAccountNoINSTALLATION_DATEBegMonth
8780000008842003-05-212003-05-01

View 3 Replies View Related

Transact SQL :: How To Write A Query To Get Current Date Or End Of Month Date

Sep 29, 2015

how to write a query to get current date or end of month date if we pass year and month as input

Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).

View 3 Replies View Related

SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row

Mar 18, 2014

I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.

Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.

View 2 Replies View Related

SQL Server 2012 :: Script To Specify Date Field To Use For Activity Date

Jun 2, 2014

I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?

The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.

Is this even possible?

View 3 Replies View Related

SQL Server 2012 :: Split Difference (number Of Nights) Between 2 Dates Into Respective Month Column

Sep 23, 2014

I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.

Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:

I need my query to output in the following format:

How to write this query?

View 9 Replies View Related

SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists

Jul 27, 2015

I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.

The Similar table would be-

create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)

But in result i want the table as -

Month Value

Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull

View 9 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

Help On Complex Date Solution Date Remain To Next Month

Apr 17, 2008

hi need help to solved date calculation for next month
i explain
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month if the dates is not for the next month i don't need to see it

like this example 09/07/2008 > 09/08/2008 (is not for next month)
like this example 10/09/2008 > 12/09/2008 (is not for next month)





555
EEE

09/07/2008
09/08/2008

4

666
fff
10/09/2008
12/09/2008

1

in this example i need to see only the relative dates for next month only in the view





333
cccc

01/05/2008
15/05/2008

4

4

333
cccc

01/05/2008
31/05/2008

1

30




tb_all_holiday before





id

fname

Start_Date

EndDate

val_holiday

111
aaaa

15/03/2008
10/05/2008

1

222
bbbb

02/05/2008
31/05/2008

3

333
cccc

03/04/2008
15/05/2008

4

333
cccc

29/04/2008
07/07/2008

1

444
dddd

01/05/2008
02/05/2008

1

444
dddd

09/05/2008
19/08/2008

1

555
EEE

09/07/2008
09/08/2008

4

666
fff
10/09/2008
12/09/2008

1
VIEW_all_holiday after -next month only





id

fname

Start_Date

EndDate

val_holiday
sum_day_next_month

111
aaaa

01/05/2008
10/05/2008

1

4

222
bbbb

02/05/2008
31/05/2008

3

29

333
cccc

01/05/2008
15/05/2008

4

4

333
cccc

01/05/2008
31/05/2008

1

30

444
dddd

01/05/2008
02/05/2008

1

1

444
dddd

09/05/2008
31/05/2008

1

22



all the time i need to see only the relative dates for the next month only

tnx

View 10 Replies View Related

T-SQL (SS2K8) :: Trying To SUM Row With Current Date To Row With Last Month Date

Jul 23, 2014

I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.

=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,

[Code] ....

Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.

SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID

View 9 Replies View Related

Getting The 1st Of The Month, Date

Feb 2, 2006

Hi all,I have a SP that is passed a date, and then need to do a test of some data returned from the DB by getting the passed Date, finding out its month, and then using the BETWEEN clause to get all rows from teh DB where their date falls between the 1st and the last (28th, 30th, 31st) of the month contained in the Date that is passed to the SP. I currently have the following SQL in my WHERE clause to get the first day of the month, but it seems long-winded. Is there a smaller, smarter way of getting it...DATEADD(MONTH, +1, DATEADD(MONTH, -1, @RequiredMonth))ThanksTryst

View 2 Replies View Related

Last Date Of The Month

Sep 3, 2003

Is there any function in sql to find last date of the given month?

View 5 Replies View Related

Certain Date Of A Month

Jul 21, 2014

I'm trying to write a query that will tell me the day of the week for the 20th of any given month.

View 6 Replies View Related

First Date In The Month

Apr 14, 2004

I have a question about creating view for these coloum and have a lot trouble, before that I describe my tables :

Table A :
Code as nvarchar
Ammount1 as money
Date as datetime -> transaction date
primary key are Code and Date1

Table B :
Code as nvarchar
Ammount2 as money
Date as datetime -> transaction date
primary key are Code and Date2

I like joining this table and create table/view that result :

Code | Amount1 Today | Amount2 Today | Amount1 Last Year | Amount1 Month to date (Sum from first day month till today) | Amount2 month to date | Amount1 Last Year month to date

The parameter only for @Date.

First I have problem to get queries specially for first date in the month, I used CAST but it doesn't work, then I used CONVERT and CAST still doesn't work.

Anybody help me ?
Thx a lot.
ad1k4r4

View 6 Replies View Related







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