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


ADVERTISEMENT

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

Convert Date To Month?

Nov 23, 2011

I am trying to convert the dates from a table to months and year in a sql query

Table name is

Sales

Field is called Sales Date

I want to convert the Sales Date to Month to work out monthly sales

View 2 Replies View Related

Convert Date To First Day Of Month

Nov 16, 2011

I have a date/time field called ConsultDate. In my query, I want to convert this into the first day of the month it falls in. So if the consult date is 11/14/2011 the converted date would be 11/1/2011.

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

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 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 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 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 :: 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 Varchar To Date?

Nov 27, 2015

Having an issue getting this done. The datatype in the table is varchar(50). The values in the table are

11122015
11122015
11122015

Nothing has worked so far.

View 3 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 :: Convert YYYYQ To Quarter End Date

May 14, 2015

I have a text field which displays quarter using YYYYQ (i.e. 20142, 20151, etc...)

I need to convert the field to a datetime representing the last day of that quarter.  For example 20142 would be 06-30-2014 and 20151 would be 03-31-2015.

View 7 Replies View Related

Transact SQL :: Convert Date Into Server Default Format

Jul 27, 2015

I have to varchar columns that contain date and time columns.

Date                    Time
22-06-2015          12:28:29

My output that I want:

Date
2015-06-22 12:28:29

Is there a function that I can convert the date format like I want.

View 8 Replies View Related

Transact SQL :: Convert Local Date Time Field To GMT?

Jul 28, 2015

I need to convert Local date time field to GMT. [Does we need to consider daylight saving and so on?]

[URL]

View 5 Replies View Related

T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month

Sep 10, 2014

This is my table and data

CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484

I need to convert into this result

CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months

View 5 Replies View Related

Transact SQL :: Based On Data Convert Datatype And Make Wrong Date As NULL

Apr 21, 2015

In the below scenario we are inserting some time related fields in Temp table.But its data type is varchar. Once data loading is finished in the temp table (Data is loading by source team SQOOP and they are unable to load if the source datatype is having Date or datetime) we have to alter the column datatypes. somehow, some character data in inserted in date columns (look at into 3rd insert statement). while altering the table it is failing. Can we do any alternative for this (Means if any varchar data that is non convertible to date can we make as null)

INSERT INTO ##TEMP_TEST
SELECT '2014-09-30','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM'
UNION SELECT '2014-09-29','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM'
UNION SELECT '2014-09-28','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM'
GO
INSERT INTO ##TEMP_TEST SELECT NULL,NULL,NULL,NULL 

[Code] ....

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

T-SQL (SS2K8) :: Compare Data In A Single Table By Month Period?

May 28, 2014

i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it

select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'

i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06

select c.* from (
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join
)

View 4 Replies View Related

Pls Help W/ A Query To Return Running Balances From The Previous Rolling 3 Month Period.

Oct 18, 2007

Please refer to this table in this discussion:






Charges

Date


1

1/1/07


2

2/1/07


3

3/1/07


4

4/1/07


5

5/1/07


6

6/1/07


7

7/1/07


8

8/1/07


9

9/1/07


10

10/1/07


11

11/1/07


12

12/1/07
What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:

If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4).
If my date parameter is 7/10/07, the charges would be 18 (7+6+5)

I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:





Code Block

SELECT a.DayCount, a.Sales, SUM(b.Sales)
FROM Sales a CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Here is the result set i'm looking for:






Charges

Date


1

1/1/07


2

2/1/07


6

3/1/07


9

4/1/07


12

5/1/07


15

6/1/07


18

7/1/07


21

8/1/07


24

9/1/07


27

10/1/07


30

11/1/07


33

12/1/07
Each date's charges are a culmination of 3 months worth of charges.

Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!

Thanks in advance!

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

Transact SQL :: Create A View For Getting Rate What Was There On Said Period

May 13, 2015

table1

id Rate start end
A01 2 01/04/2015 31/03/2016
A02 2 01/04/2015 31/03/2016
A03 2 01/04/2015 31/03/2016
A01 2.5 01/04/2016 31/03/2017
A02 2.5 01/04/2016 31/03/2017
A03 2.5 01/04/2016 31/03/2017

table2

id Received
A02 13/05/2015
A02 23/07/2016

View 3 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 :: Checking Overlapping And Lost Period

Jul 8, 2015

how can I check the overlapping and the LostPeriod by chargeid?

create table #forum (contractid int, chargeid int, ByFrom date, ByTo date)
insert into #forum values ('7','18','2005-04-01','2007-03-31'), ('7','19','2008-06-01','2010-03-31'),
('7','20','2014-04-01','2015-06-01'),
('8','10','2003-10-01','2005-03-31'),('8','11','2006-12-01','2007-07-31'),
('9','11','2003-10-01','2005-03-31'),('9','12','2004-10-01','2015-03-31')

As lost period I mean that period that is not covered by any chargeid. By overlapping I mean having two or more charge id in the same period.

View 9 Replies View Related

Transact SQL :: Check Result Of In Between Two Different Times In Week And Particular Time Period

Oct 16, 2015

I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.

View 2 Replies View Related

Grouping By Date Period

May 12, 2006

I'm using SQL Server 2000.

Example table:
PeopleID Date Status
1 2004-01-01 True
1 2005-01-01 True
1 2006-01-01 True
2 2004-01-01 True
2 2005-01-01 False
2 2006-01-01 True

I'm trying to find a way to query whether or not someone has had a specific status for 3 years in a row. As you can see from the table above, PeopleID 1 has had a "Status" of "True" for 3 years in a row, whereas PeopleID 2 hasn't--there was one year where they had "False".

I'm wondering I can query this, or if I'm going to have to scan the records manually. :(

I suppose I could write a stored procedure and do some looping too.

Appreciate any help, thanks!

View 2 Replies View Related







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