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
ADVERTISEMENT
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
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
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
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
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
Nov 15, 2007
Hi, I really need this help urgently.
I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
con1.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)"
'Set the connect the command object should use
cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet
da.Fill(ds)
con1.Close()
If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then
Dim objEmail As MailMessage = New MailMessage
objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg"))
objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName")
objEmail.Priority = MailPriority.Normal
Dim SmtpMail As New SmtpClient("servername")
SmtpMail.Send(objEmail)
End If
End If
End Sub
Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy)
Realli veri urgent Thanks so much for ur'll help
View 8 Replies
View Related
Dec 11, 2007
I need to do the following and am hoping someone can help me out.
I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to thestored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the datepassed into the stored proc needs to be compared to todays date. Todays date must be determined inthe SQL.
So basically here is my pseudo code for what I am trying to accomplish. Basically I just am afterthe comparison of the two values:
If @BeginDate < TodaysDate
The difficult part is how to obtain the value for "TodaysDate"
Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in.
Can someone please code this out for me in Microsoft SQL. I would be forever grateful.
View 1 Replies
View Related
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
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
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
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
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
Jun 13, 2008
Hi,
I have a table with 3 columns inside
- record_id (int)
- user_id (varchar)
- login_date (date)
it is a many-to-many relationship table that record login date of users
Now, I want if I want to COUNT the users who login before 31 May 2008, I would use
SELECT COUNT(*)
FROM table1
WHERE login_date < '2008-31-05'
That's works
But the problem is I want to split the result to
- How many people have login in the last 0-5 days (based on the input (31 MAY 2008))
- How many people have login in the last 6-10 days (based on the input (31 MAY 2008))
How do I do that?
Please help
View 5 Replies
View Related
Jun 20, 2008
I have table:ArticleIDSubjectBodyDateThe Date field have data:12.02.200512.02.198912.02.156314.09.134530.12.345I need to show Articles wiich have date today. Example:If today is 12.02.2008 I should show only Date with value 12.02. The results:12.02.2005
12.02.1989
12.02.1563 I try something with DAY, Returns an integer representing the day datepart of the specified date but it's doesn't work.Help :)
View 5 Replies
View Related
Feb 2, 2005
How do you compare date in sql, it is giving me errors,
I want to view all that logged onto my system Today
(*Yes I want to use dyn query for this *)
LastLogin is of type DateTime
SELECT @myStatement = ' SELECT LastLogin, surname
FROM #TEMP WHERE ((LastLogin ='+CONVERT(datetime, @LastLogin)+')
exec(@myStatement)
Thanks in advance
View 4 Replies
View Related
Nov 4, 2005
I want to know how can I write a sql to compare 2 dates which can run in SQL Query Analyzedeclare @date1 datetime, @date2 datetime
set @date1 = '2005-01-22 12:00:05'set @date2 = '2005-01-22 13:05:01'how can I compare whether @date1 = @date2 in terms of (YYYY-MM-DD) only?
View 3 Replies
View Related
Nov 30, 2007
Hi Guys,
I have a table "HOLIDAY" with column "HOLIDAYSTART" and "HOLIDAYEND" in date/time type.User can apply new holiday on a form with value "STARTDATE" and "ENDDATE", but how do I check if the new holiday dates already exist in the table??
I looked around and found you can use something similar to following to check for single date,select * from holiday where ('1-12-2007' >= HOLIDAYSTART) AND ('1-12-2007' <= HOLIDAYEND)
but how would I do it with my situation above with between 2 dates? any help? Thanks in advance.
View 3 Replies
View Related
Aug 2, 2004
I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?
Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.
Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader
conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")
While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While
Response.Write("</td></tr></table>")
dtrAuthors.close()
conPubs.close()
View 1 Replies
View Related
Aug 2, 2000
I want to write a query to retrieve all the items that are
order between 2 dates range, 07/30/2000 to 08/02/2000, any suggestions are
greatly appreciated,
View 4 Replies
View Related
Jan 10, 2006
I have a preexisting database that has dates in the format mm/dd/yyyy but it is set up as varcar. How can I apply numeric operators to it to find how old someone is from todays date? I then need to take that value and populate another column on the same row.
View 1 Replies
View Related
Jan 4, 2006
Hello, I am wondering how to select the max date from a database view containing multiple date fields for a particular record. I am rusty at SQL and can't seem to get started. Here's an example of the data:
PRCL_IDPROJ_STRT_DTORD_DT ...
1242/3/20063/5/2006
5206/3/20068/2/2006
6412/31/2005
1872/14/20063/16/2006
I need to be able to compare the max date for each PRCL_ID record ("where prcl_id="). In the example above, prcl_id 124 has a max date of 3/5/2006, and prcl_id 520 has a max date of 8/2/2006, etc. The results of the SQL query should return the PRCL_ID and the max date.
Example query results:
PRCL_ID ORD_DT
124 3/5/2006
Please let me know if you can help or if I can provide more info.
Thanks,
Alexkav
View 6 Replies
View Related
Aug 2, 2004
I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?
Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.
Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader
conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")
While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While
Response.Write("</td></tr></table>")
dtrAuthors.close()
conPubs.close()
View 1 Replies
View Related
Oct 10, 2005
Hi,
I need to compare 2 periods (start date / end date) in order to find out if the first period overlaps or in included in the second period.
any idea ???
thanks
View 14 Replies
View Related
Sep 25, 2013
From my table:
convert(varchar,Rec.expdate,101) as exp:
01/19/2038
08/12/2013
08/12/2013
I only want record is >= the current date but looks like my syntax is not working right because I got nothing return instead of 01/19/2038.
and convert(varchar,Rec.expdate,101) >=convert(varchar,getdate(),101)
View 3 Replies
View Related
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
Sep 3, 2003
Is there any function in sql to find last date of the given month?
View 5 Replies
View Related
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
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
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