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

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

Compare Month In SQl

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

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

How To Compare Month With SmallDateTime Field?

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

Compare Only Month And Year Part In Datetime Type

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

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

Transact SQL :: Compare Event Time Value With Current Year And Month

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

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

Super Urgent Codes To Compare Datafield Date With Today's Date

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

How To Get Todays Date In Format YY/MM/DD And To Compare It To Another Date Passed Into The Sql

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

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

How Do I Compare Date In MS Sql

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

Compare Date Value In SQL SP

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

Date Compare

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

SQL Compare Date

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

SQL Statement To Compare Between Date

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

How Do You Compare Date Values

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

Compare Date Range

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

Compare Varchar Date

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

SQL- How To Compare Max Date Across Fields

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

How Do You Compare Date Values

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

How To Compare Date Periods ?

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

Compare With Current Date

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

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

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







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