Transact SQL :: How To Sort Date By Month

Jun 9, 2015

I have a filed named 'date'. I convert this into 101 format. Now I want to sort that date by year not by month.

View 12 Replies


ADVERTISEMENT

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

Transact SQL :: Date Between 1st And 10th Of Every Month

Nov 12, 2015

I have the following requirement I need to extract all records for min or max date between the 1 and 10th of every month, the following query works but I find some dates that are not in the range specified.

Select
[EventType],ItemName,FORMAT([TimeLastModified],'MM')asFromMonthValue,
FORMAT([TimeLastModified],'MM')asToMonthValue,
FORMAT([TimeLastModified],'MMM')asFromMonthLabel,
FORMAT([TimeLastModified],'MMM')asToMonthLabel,  
min([TimeLastModified])asMinDate,max([TimeLastModified])asMaxDate,ModifiedBy,Casewhen[EventLog].EventType=1
Then 
'Item Added'

[Code] .....

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

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

Transact SQL :: Subtracting Month And Day From Current Date

Nov 19, 2015

I am trying to get a query that subtracts a month and a day from current date in SQL Server.

Currently I have SELECT  DATEADD("MM", -1,GETDATE()) which subtracts one month from the date but I want to subtract a month and a day from date.

View 9 Replies View Related

Transact SQL :: Convert GL Period To Last Date In Month

Oct 13, 2015

I have a table that records a GL period as a string. I want to convert this value to the last date in the month. 

GlPeriodID
APR 2015

I want to convert to 
4/30/2015

View 7 Replies View Related

Transact SQL :: Number Of Days Between A Date And Its Month End

Sep 23, 2015

I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
 
Something like below,
 
DateTD       Days left
2009-01-05  14
2009-01-06      13
2009-01-07      12
2009-01-08      11
2009-01-09      10
2009-01-12       9
2009-01-13       8
2009-01-14       7
2009-01-15       6
2009-01-16       5
2009-01-19       4
2009-01-20       3
2009-01-21       2
2009-01-22       1
2009-01-23       0
2009-02-02       /
2009-02-03       /

View 28 Replies View Related

Transact SQL :: Loop A Month In A Specified Date Range?

Jul 23, 2015

I am trying to query a code where i need to loop a month in a specified date range. Inside the loop I need to return a result of data each month and need to update the table of the returned data. How do I do the update a field inside the loop? Here's my query:

declare @table1 table (
YEAR_EFF int,
MONTH_EFF int,
IDNumber (8),
SUBS_CNT smallint,
MEM_CNT smallint)
declare @StartDate datetime,

[code]....

Others says I need to use exec sp_executesql N'' but how do I use it using my code above?

View 7 Replies View Related

Transact SQL :: Recognizing Month And Proportion In A Date Range

Oct 8, 2015

I need to recognize only one month in a date range e to make a proportion of the quantity. Practically, period 31-08-2015 - 30-09-2015 is 31 days, 1 belonging August and 30 belonging September so 3.2258% of the quantity must belong August and 96.7742% September. The quantity 200, so 193.54 belong September (That's what I need to achieve). Range 01-09-2015 / 30-09-2015 Qty 500, all 500 belong September.

Range 01-07-2015 / 20-08-2015 Qty 2500 0 belong September. A little bit more complicated if I got 25-06-2015 / 16-12-2015. it 30 day for September and with a datediff I can count the days and make a proportion. I can write piece by piece the code but I'd prefer of course to have only one query for this.

The DDL:
create table forum (idd int, byfrom date, byto date, qty int)
insert into forum values
(1,'2015-06-15','2015-08-18',300),(2,'2015-09-16','2015-10-04',400),(3,'2015-07-28','2015-09-27',1000),
(4,'2015-09-01','2015-09-30',500),(5,'2015-09-03','2015-09-03',300),(6,'2015-08-02','2015-09-02',100),
(7,'2015-07-01','2015-07-30',500),(8,'2015-06-03','2015-12-08',500),(9,'2015-09-01','2015-09-30',500),
(10,'2015-08-04','2015-09-04',300)

View 14 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 :: Convert Today Date To MMDDCCYY To A Decimal And Retain 0 In Front Of Month

Aug 24, 2015

This seems to get the job done...

SELECTRIGHT('00000000'+CONVERT(VARCHAR,REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')),8)

However, when I try to create it and CONVERT it to a DECIMAL, it then loses the "0"

SELECTCONVERT(DECIMAL(8,0),RIGHT('00000000'+CONVERT(VARCHAR,REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')),8))

Is it impossible to convert it to a decimal and retain the leading "0" on the month?

My vendor's spec states..."Business Date Numeric For 8 positions MMDDYYYY"

View 5 Replies View Related

Transact SQL :: Split Date Range Into Monthly Wise And Loop Through Each Month To Perform Some Operation

Oct 20, 2015

Let's say if the date is 01/01/2015 till 01/01/2016

I want split these dates monthly format wise and then use them in variable in cursors to loop

For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016

View 2 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

Transact SQL :: Displaying Sales Data In A Month By Month Grid

Aug 11, 2015

Most of the data is in one table. 

Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264

I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.

 SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January', 

View 2 Replies View Related

Sort By Month

May 5, 2006

Hi,

I've a field with month & year. I'm trying to sort this column. Something like this is my column values.

April 2001
August 2001
December 2001
February 2001
January 2001
July 2001
June 2001
March 2001
May 2001
November 2001
October 2001
September 2001
April 2002
August 2002
December 2002
February 2002
January 2002

But I want to sort this like below.

January 2001
February 2001
March 2001
April 2001
May 2001
June 2001
July 2001
August 2001
September 2001
October 2001
November 2001
December 2001
January 2002
February 2002
March 2002
April 2002
May 2002

I'm not finding any way to do this. If I sort this list by month, it'll be sorted alphabetically. Any help on this would be much appreciated!

Thanks

View 4 Replies View Related

Sort Order By Month

Nov 6, 2007

SELECT TOP (100) PERCENT Calendar_Key, Calendar_Date, Calendar_Year, Calendar_Month, Calendar_Quarter, Calendar_week, Calendar_DayOfMonth,
Calendar_DayOfYear, Calendar_Datename, Calendar_EndOfMonth, Calendar_EndOfQuarter, Calendar_EndOfYear,
CAST(CASE WHEN Calendar_Month = 1 THEN 'January' WHEN Calendar_Month = 2 THEN 'February' WHEN Calendar_Month = 3 THEN 'March' WHEN Calendar_Month
= 4 THEN 'April' WHEN Calendar_Month = 5 THEN 'May' WHEN Calendar_Month = 6 THEN 'June' WHEN Calendar_Month = 7 THEN 'July' WHEN Calendar_Month
= 8 THEN 'August' WHEN Calendar_Month = 9 THEN 'September' WHEN Calendar_Month = 10 THEN 'October' WHEN Calendar_Month = 11 THEN 'November'
WHEN Calendar_Month = 12 THEN 'December' END AS char(9)) AS Month
FROM EDW.Calendar_Dim
WHERE (Calendar_Year IS NOT NULL)
ORDER BY Calendar_Year, Calendar_Month

I have two vlaue prompts.. which show year & month, when we select year, it shows in order. when we select month, since its character, it showing from 'April, August....

but i want month prompt to start from Jan...

Any suggestion on how to modify..

thanks
Phanicrn

View 9 Replies View Related

Sort Table By Month

Sep 10, 2007



I have a table report that lists the prior 12 months of sales data. I'd like the report to display in reverse order from current month back. When I sort decedning by month it puts the months in reverse alphabetic order. How can I sort by numeric order?

Thanks.

View 1 Replies View Related

Sort By Year Order, Not By Month

Feb 27, 2008

When I sort the effective date, it sorts it in month order with all of the years together under that month.

Is it possible to sort the effective date in year order? The way it if formated right now is 01-01-2007. Is there any type of formula that I can use?

Below is the field that I use.
CONVERT(CHAR(10),pcsp.pcsp_eff , 110)

View 5 Replies View Related

Need To Return Month/YYYY And Sort By It In Report

Aug 20, 2007

I have a report that is grouped by Month and Year.

So I need it to display:

Jan, 2007
Feb, 2007
Mar, 2007
Apr, 2007
Etc…

AND it needs to be sorted with Jan as the first month and Dec as the last (as if it was 1-12 for months).

Now I can get it to display by taking the month name and concatenating it to the year, but then of course it won’t sort the way I want it to.

Any suggestions on this?

Perhaps I could/should return two values, the first being what I display on the report, and the second being what I sort on as a hidden field? So I could return 200701, 200702, 200703, and so on, to sort on that?

View 2 Replies View Related

Reporting Services :: How To Sort Data In SSRS Based On Month And Year

Sep 30, 2015

I have below report with following data

Date                      Count
April-2015             100
January-2002        30
November-2010     55
July-2000               10

What is the best approach to sort this data based on date in a tablix and also sort the date in Column bar chart?

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

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

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

Transact SQL :: Sort Column With Null

Nov 26, 2015

I have column X:

X
-------
1
NULL
4
NULL
2
3

NULLI need to sort this column to get this output: X
-----
1
NULL
2
NULL
3
NULL
4
NULL

What i need to do?

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

How To Sort The Date ?

Oct 12, 2006

SELECT
LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' +
RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date,SUM(CASE  a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming,SUM(CASE  a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design,SUM(CASE  a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload,SUM(CASE  a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing,SUM(CASE  a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting,SUM(CASE  a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as OthersFrom
task_table a,act_table b where a.status_id=b.act_id and
a.user_id=(select user_id from user_table where user_name='Raghu') and
a.task_date like '%/%/2006' GROUP BYLEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4)Output :Aug 2006  294       0    0    80      0       0    Jan 2006    14        0    0    0      0         0    Oct 2006  336       0    0    0        0       0    Sep 2006  3262    20    24    8    16    0    How to sort the date in ascending Order ?Jan 2006Aug 2006Sep 2006Oct 2006

View 2 Replies View Related

Transact SQL :: Query To Return Greater Than Zero Values To Sort Up And Zeros Go Down

Sep 17, 2015

I am using a table to store different size numbers for example:

Look at the picture below:

But I want this type of output look at the picture below:

How to sort the query to return greater than zero values to sort up and zeros go down. 

I am using sql server 2008.

View 14 Replies View Related

Transact SQL :: Order By Month

Apr 21, 2015

I am using below query i want to order it by month so that data is displayed like

Select (count(I.Colour)) as Litres,
DATEName(MONTH,ShipDate) as [Loaded Date] from InkData I
group by DATEName(MONTH,ShipDate)
order by DATEName(MONTH,ShipDate)

Right now it is not ordering by month, it is ordering alphabetically.I want this to be ordered like

Jan
Feb
March

View 6 Replies View Related

Transact SQL :: Getting Name Of Month In A Certain Language

Nov 10, 2015

Is it possible to return the name of a month in a certain language?

I know I can use SELECT DATENAME(month ,GETDATE())to get the name of the month, but it will appear in the language of the OS regional settings (?)Is it possible for instance to make it appear for instance in English or Portuguese, adding any SET command?

View 3 Replies View Related

How Do You Sort On Two Date Fields ?

Nov 29, 2006

I am trying to order two date columns. One Ascending theother Descending. I am using the Orders table of Northwind in SQLServer. No idea why, but it only sorts on the first column I setcriteria on. I think you have to convert it to date as it gets storedas date/time...I triedSELECT     TOP 100 PERCENT OrderID, CustomerID, EmployeeID, OrderDate,CONVERT(char(10), RequiredDate, 103) AS RequiredDate,CONVERT(char(10),                     ShippedDate, 103) AS ShippedDate, ShipVia,Freight, ShipName, ShipAddress, ShipCity, ShipRegionFROM         dbo.OrdersORDER BY RequiredDate, ShippedDate DESCBut odd results occuredAny ideas appreciated

View 3 Replies View Related







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