How To Compare 2007-9-11 And Month(GetDate()) ?
Oct 30, 2007
I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime)
I write code
select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1)
it will like
select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1)
→ select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30
but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code
pleae help... thank you very much
View 1 Replies
ADVERTISEMENT
Jan 13, 2006
I need a where clause like
Where field=getdate()
but I can't get it to work because the field is datetime so there is never an exact match. Do I need to break out each part just to compare today's date to the 'date' of getdate()
View 14 Replies
View Related
Aug 22, 2005
OK, simple question I am sure...
I have a very simple SQL command:
select convert(varchar, getdate(), 107);
I get the result:
Aug 22, 2005
How can I get it to say August 22,2005?
View 1 Replies
View Related
Apr 11, 2008
How can i return month in the format of 'April'.
When i am trying select month(getdate()) i am getting 4 but i am looking for text format - April.
Please correct format.
Thanks
Dk
View 2 Replies
View Related
Dec 10, 2010
How to get First day of previous month and last day of previous month(From getdate()) using SQL..?
View 13 Replies
View Related
Mar 7, 2008
I am trying to calculate the work experiance of an employee. I am using the function DATEDIFF for that.
For eg:
Code SnippetDATEDIFF(yy,JoiningDate,GETDATE())
But this will display the date difference in years, if I use mm, instead of yy then I will get the result in months.
But I want to dislay the result as a combination of year and month, like if an employee has 13 months of experiance then i want to get the result as 1.1 years. Is that possible using SQL?
Please help. All your helps will be appriciated.
View 12 Replies
View Related
Dec 21, 2006
I tried to find it on the Internet, but was unsuccessful. If someone can help me with a select statement that will return JUST the current month and year or day and month. I would really appreciate it.
Thanks
View 4 Replies
View Related
Mar 10, 2008
Hi,
I have two varchar column in SQL.there im storing Month name
Lets say,
col1 col2
JAN APR
MAY DEC
Is there any possibility to use the sql query select * from tablename where DEC between (col1,col2)
I just wonder since it is varchar field and not date field can i use this syntax to compare ? or any other better way to achieve this?
Thanks for any response.
Regards,
RR
View 1 Replies
View Related
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
Apr 29, 2008
I have a SmallDateTime data type field. The SmallDataTime contains day/month/year hour:minuteeconds AM/PM. What I wanted to do is create a store procedure that will only take the month of the field and compare that to my variable. So for example, I wanted to something like the following:
@month varchar(50) = "3"
SELECT * FROM myTable WHERE monthField = @month
Any help is much appreciated.
View 8 Replies
View Related
May 19, 2008
hai friends,
iam doing a project in .net and using sql server.
i need to compare only month and year part in datetime type to retrive data.
1)retrive unique year and its months available in the database.
like may 2008
apr 2008
mar 2007
View 3 Replies
View Related
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
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
Mar 3, 2008
Hi
everybody.
I am new to VBA,.,, I Have created a folder under inbox in Outlook 2007 which stored the receipients information
like name, E-mail, which is not present in the address book of outlook.
I have imported this folder in access 2007. My problem is ... it does not display the email address.
Rest of the informations is displayed... Is there any way i could do this.??
is there any way i could get the email addresses to appear(import/get) in to the access table???
Is there any module i mite have to create??
Plz help...
thanks a million
View 1 Replies
View Related
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
Oct 4, 2000
Please i need an exmple of ur solution, thanks :)
I'm using some files to show certain pages on certain date for an example
File name : aa.doc
start date: 10/02/00
end date : 10/03/00
But it expires on 10/02/00, here is the strored procedure:
Before the date comes, it expires the page
Here is my stored procedure:
"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "
Thankx a lot
View 1 Replies
View Related
Apr 22, 2015
following table global_usage
ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
View 6 Replies
View Related
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
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
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
Sep 26, 2007
select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)
From above query I get
mmddyyyyhhmm
but it' s yyyy and hour can not be separated
04/12/200702:05
How can I separated the year and hour ?
Thanks
Daniel
View 2 Replies
View Related
Aug 2, 2002
Does anyone know how I can get last day of month
if I pass a function a given month and and given year.
@Month = 2
@Year = 2004
The result I would need is 29 because there are 29 in
the month of February in the 2004.
Any help on this is greatly appreciated.
Kellie
View 1 Replies
View Related
Jul 22, 2007
Table MediaImportLog
column ↘ImportIndex ImportFileTime ImportSource
value ↘80507 20060506001100 815
80511 20061109120011 CRD � P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table BillerChain
column↘BillerInfoCode ChainCode
value ↘750 815
value ↘81162 CRD
Table Biller
column↘CompanyCode BillerCode
value ↘999 750
value ↘81162 516
TAble DataBackup
column↘CompanyCode Keepmonth
value ↘999 6
value ↘81162 12
---------------------------------------------------
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?
thank you very much
View 3 Replies
View Related
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
Sep 10, 2007
Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed
September:
@CurrentDate = 9/10/2007
@EndDate = last working day 9/30/2007
Total# of working days = 15
October:
@CurrentDate = 10/1/2007
@EndDate = last working day 10/31/2007
Total# of working days = 23
November:
@CurrentDate = 11/1/2007
@EndDate = last working day 11/30/2007
Total# of working days = 22
etc.
Any ideas of how i can approch this?
Thanks in advance.
View 3 Replies
View Related
Dec 11, 2006
i have some classes that I want to group by month/year (note:i dont need the day of the month)
how do i wirte my sql so it only gives me the dictinct groups month/year of the classes I have so that it comes out like so..
11/2006
12/2006
1/2007
3/2007
i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.
dbo.classgiven.classdate date of the class.thank you all
SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/" + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)", conNorthwind )
View 9 Replies
View Related
May 13, 2008
When I use the getDate() function I get something like
2005-03-09 15:13:45.017
but I don't want the time, just 2005-03-09
how do you strip out the time from the result?
Thanks
View 2 Replies
View Related
May 28, 2008
How can i getdate in C# code, like we do in SQL Server ?
Thanks.
View 1 Replies
View Related
Apr 27, 2001
However, when I run select getdate() in Sql 6.5 I get the following
Apr 27 2001 11:59AM
Both queries are run from the QueryIsql window.
How can I get the sql 7 version of getdate to be exactly the same as the
sql 6.5 version from the query window without resorting to usign the
convert function?
According to Sql 7 books online, getdate
"Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values"
View 1 Replies
View Related
Oct 3, 2000
I'm facing some strange problem here:
-----------------------------------------
I'm using some files to show certain pages on certain date for an example
File name : aa.doc
start date: 10/02/00
end date : 10/03/00
But it expires on 10/02/00, here is the strored procedure:
Before the date comes, it expires the page
Here is my stored procedure:
"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "
Thankx a lot
View 2 Replies
View Related
Sep 20, 2000
i am also trying to write a query that
retrieve information on yesterdays new registered
member. So far as i am concern with sqlserver you use
the getdate()-1 to retrieve this information. But the
problem i get when i try to use the getdate()-1 is
that i tend to get todays and yesterdays registered
users. Is there anywhere that i would be able to
retrieve information on just yesterdays new registered
members and also only todays new register members.
View 1 Replies
View Related
Sep 28, 2000
The following WHERE clause does not generate a datetime from my GETDATEs. Can anyone help me out on the syntax?
WHERE (FROMTIME = GETDATE() AND ToTime = GETDATE())
Thanks much.
TM
View 7 Replies
View Related
Aug 7, 2003
Is it right, that getdate() cannot be used in an user defined function. if so, then how do i get hte current date and time in my function?
View 2 Replies
View Related
