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


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

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

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

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

Date : First Day Of Last Month

Jun 2, 2008

Hello all,

Is there a way to set a variable that includes the first day of the last month.

For example, we are june 2nd. I want a function that returns - for every day in june - 2008/05/01.

Thanks,

Raph.

View 14 Replies View Related

First Date Of The Month

Nov 23, 2005

Hi guys,

How to get the first day of the month.

Say the given Cut Off Date is 11/30/2005. I need to get the StartDate as 11/01/2005.

Thank you.

View 2 Replies View Related

Month Name From A Date Value

Sep 27, 2006

Hi All,

Can anyone tell me the function name by which I can get the month Name form a date value

When I am using the command print month('2006-09-26 16:30:18.793')in SQL Analyzer window it is giving 6as output but i need the name i.e. JUNE

View 2 Replies View Related

Date / Month

Jun 18, 2007

I need to sort some data by month or extract data for a particular month. My date format is 5/7/2007 11:38:54 AM. What is the easiest way to achieve this?

Melvin Felicien
IT Manager
DCG Properties Limited

View 4 Replies View Related

Parameter Selection Of Month, Showing Selected Month And Sum Up To That Month In Another Row

Apr 5, 2008

Hello what I'd like to display the following in a matrix report:

Parameter selected: 3 (March), 2008 (Year)

Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...

So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.

View 3 Replies View Related

How To Compare 2 Date By Month?

Jul 30, 2007

I'm doing DTS, Here is one of my Sql Query in DTS
 
Select * From ZT_DailyRpt_Detail Where isNumeric(TenDayDate) = 1 And TenDayDate < Convert(varchar(10),DateAdd(Month,-CAST                              ((SELECT         keepmonth                                  FROM             zt_databackup a, zt_biller b                                  WHERE         a.companycode = b.companycode AND                                                             zt_DailyRpt_Detail.biller_code = b.billercode) AS int),GetDate()),112)
 
the sub query ?SELECT         keepmonth                                  FROM             zt_databackup a, zt_biller b                                  WHERE         a.companycode = b.companycode AND                                                             zt_DailyRpt_Detail.biller_code = b.billercode) AS int】will return a value 6 or 12
for the reason, every month have differnet days, ( some have 31 days , some are 30 days ) I don't want to check the day of date , only to compare month 
if sub query return "6" and I do DTS on 2007/07/29 , will select  date which TenDayDate< 2007/01 not  TenDayDate<2007/01/29 ( don't want to check the day of date)
 
does my query correct? if not can you correct it for me? thank you very much
 

View 2 Replies View Related

Date Query Using Month Name

Mar 17, 2004

Hello,

I have made a query that does a cross-tab of data. The date data is normal like 02/04/2004, 03/04/2004 etc.

I am using Month Name to group the dates by Month name.

My problem is the query result sorts the month names in alphabetical order, not month order.

I am using the query to generate a graph and the months are not in order.

Is there a way to sort the months by month order not alphabetical order.

Thanks
Goong

View 4 Replies View Related

How To Find Date Of Month

Apr 20, 2006

Hi
Problem:
one table "tbl_SalesReporting" in this table salesEr every day submit his information.
i need which day salesEr not submit his information. this is check up to current date.
E.G:
salesEr submit information start this date "1/04/2006" to "17/04/2006" and
currentdate: 20/04/2006
i need 18/04/2006,19/04/2006 this date Using SQL queries........
Please help me........
 

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

3 Month To Date Average

Apr 1, 2008

Hello Friends

Actually i posted this problem earlier , hoping that i got the result , did not look in depth untill i realized the values i got was getting was wrong.

The Scenario is still the same.

3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.

I am creating a Calculated Member as measure.

Basically if i select the average on 76th day of the year, it should basically be the average of 76th Day ( well offcourse whould ignore the empty cells )

For Example the result set should be very similar , It is basically doing MTD

Running balance is basically adding up , where as 3 month to date average should be average of 3 month previous average

suppose today is 75th day , so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)

I have used the lag function

AVG(Hirearchy.Currentmember.lag(2):Hirearchy.Curre ntmember, Measure.abc)

where hirerachy is Year->Month-date

The problem i have here is which i am coming close to conclusion

When i use month level it gives me average of month level , the result on month level is fine. But my requirment is to have it on date level. but how do i have rolling average of 3 month in a date level, if i do a date level with 90 days lag which is not correct which is average of 90 days from current day.

AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Ab1_Avg]

)


When i drill down to date level , which would be assumed

AVG([Tbl Date Key].[Report Hirerachy].date.Lag(2):[Tbl Date Key].[Report Hirerachy].date,[Measures].[Ab1_Avg]

)





its doing a lag on 3 days lag as appose to , I need the lag on 3 months on a day which would be 90 days

should i be doing a lag on days.



The problem at 90 days lag would be every it would lag 90 days average , but what i am looking for it is when it is on the middle of the month it should be

suppose today is 15th day of month, so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)


i am confused , please help

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

Current Date Last Month

Apr 22, 2008

hi,
If today is the 22/4/08
how can I get the day last month,
eg. 22/3/08

?thank you

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

Selecting A Particular Date The Following Month

Dec 20, 2006

Hi,

I've been wrestling with this problem for a while, but my newbie SQL skills are no match for it, so I'm hoping somebody here can point me in the right direction.

I have the following table, called AccountPayments:


PaymentID CaseID_FK AmountPaid InvoiceDate PaymentDate

12135 250 01/16/2006 01/31/2006
13136 150 01/16/2006 01/31/2006
14 135 200 01/24/2006 02/05/2006
15 136 300 01/24/2006 02/05/2006
16139 120 01/25/2006 02/07/2006
17 136 100 01/25/2006 02/10/2006
18 137 115 01/25/2006 02/13/2006
19 136 110 02/12/2006 02/25/2006
20139 310 02/12/2006 03/06/2006
21 138 220 02/15/2006 03/09/2006



I would like to select all the entries where the payment date is, at the latest, the 7th day of the month following the one in which the invoice was issued.

In other words: If the invoice date is in January, I would like to select all the entries where the payment date is February 7th at the latest. If the invoice date is in February, I would like to select all the entries where the payment date is March 7th at the latest.


So, for the above table, I would like to get the following result:


PaymentID CaseID_FK AmountPaid InvoiceDate PaymentDate
12135 250 01/16/2006 01/31/2006
13136 150 01/16/2006 01/31/2006
14 135 200 01/24/2006 02/05/2006
15 136 300 01/24/2006 02/05/2006
16139 120 01/25/2006 02/07/2006
19 136 110 02/12/2006 02/25/2006
20139 310 02/12/2006 03/06/2006


Does anybody know if it's possible to do this? I'm working with SQL Server 2000 and have been playing around with dateadd, but I can't seem to figure it out.

Thank you for your help.

View 4 Replies View Related

Get Literal Month From Date

Oct 25, 2007

I am trying to extract the month from a date field, and I am able to get the integer for the month, however, when I try to convert the integer to the literal, e.g. 3 to March, I am having issues.

I have a field called PROCES, which represents a date processed, which is numeric(8).... i.e. 20061231.

I am using:
SELECT datepart(mm,cast(convert(char(8),PROCES)as datetime))
which gives me the correct integer.

When I try:
SELECT datename(mm,datepart(mm,cast(convert(char(8),PROCES)as datetime)))

I get January for all records...?

Does anyone know why, and how to fix.

Thanks

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

How To Get One Perticular Date Of Each Month

Oct 9, 2007



Hi All,

I want to include a column to my report. The column name is Payment Due date. Payment due date is 05th of the each month. There is no field in data base tables called payment due date. Can anyone help me how to write the code to get pay ment due date for different customers.

As a example

Payment Due date can be 01/05/07 or 12/05/06 or 03/05/07. But its always 5th of each month.

Thanks

View 3 Replies View Related

Getting The Date For 1st Of Last Month In SSRS

Dec 12, 2007

Hi All,

I am trying to get the 1st date of the previous month in SSRS and either displaying it or using it in a variable as a calculation or as a value for a filter query based in SQL. However, when I use the SQL equivalent it does not display anything. The SQL code is as below:

- DateAdd("mm", DateDiff("mm", 0, DateAdd("mm", -1, getdate())), 0)

I know I have to substitute the getdate() with today() in SSRS. Even then it still returns no value. Does it have anything to do with the DateInterval value of DateAdd() and DateDiff()? I have changed it to 'DateInterval.month', but then it says that the month portion is not recognised. Also, is there a DateInterval.week that can be used?

Your help is much appreciated.

Thanks & Regards,
Mohd Fadzli

View 5 Replies View Related

Find First And Last Date Of A Month?

Jun 18, 2007

Afternoon all,



Just a quickie, is there an expression built into Reporting Services from which you can find the first date and last date of any given month? The first date isn't much of a problem as it isn't quite as dynamic as the last date.



I can do it in an IIf statement but wouldn't be able to make it recognise 29th February in a leap year very easily.



Thanks,



Paul

View 4 Replies View Related

Help To Extract Month From Date

Jan 9, 2008



I need to store 01-jan, 02-Feb ----- 12-Dec in another column which is extracted from the date column in the same table
can someone tell me how to do this
I need to havem month number as 01 not simply 1.

View 5 Replies View Related

Month And Day Date Selection

Aug 3, 2007

Hi Folks:


I'm running a query whereby my users will select between "FromCloseDate" and "ToCloseDate". The easy part is when they're searching for month, day and year between the close dates however, they have a boolean report parameter that allow them to select month and day between the close dates. Has anyone done a between date selection for month and day?

Thanks in advance

View 1 Replies View Related

Start And End Date Of Previous Month

Aug 15, 2006

I need start and end date of previous month, such as StartDate=07/01/2006 and EndDate=07/31/2006 for currentdate 08/156/2006
How can I do this?
 

View 1 Replies View Related







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