Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







SELECT Where Date &>= 14 Months Ago


Hi,

I need a select statement with the condition that the date (a timestamp with format yyyy-mm-dd) is either equal to or longer than 14 months from today's date.

I am using SQL Server

Can anyone point me in the right diretion?

Thanks


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To Get Date From 3 Months Ago
Hi all,How to get the date from the 3 months ago? So, if user enter a date (10/10/2006), how can I get to 07/10/2006.Thanks in advance...

View Replies !   View Related
Date Difference In Months
Hi to all,

I want to make an sql query that will subtract two dates and the difference will be number in months.

i have this table TestTable with Fields ACost, Adate, AMonth

my formula is this X = (Now-Adate), should give me number in months
and Y = (ACost/AMonth)
i want to get the product of X and Y, (X*Y).

how can i make this in sql views?

can anyone help me with this?

thanks a lot!


best regards,

saldiboy

View Replies !   View Related
Expression Where Date Is 3 Months Ago?
What is the correct code for an expression where my "datetime" field is greater then or equal to 3 months ago?

View Replies !   View Related
Date In Current Year Or Last Two Months
Hello

I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.

So, this is what I have.......




Code Snippet

where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end





Is there a neater/better way of coding this?

Jon

View Replies !   View Related
Select Last Months Data
Hi, i am trying to create a t-sql statement that will retrieve last months data (ie. if i run the query on 9th August, i only want to retrieve Julys data, 1st Sept will retrieve all of Augusts data etc). The query will be used once a month to populate a table, can anyone advise me on the correct where clause to use ?

Thanks in advance

Dave

View Replies !   View Related
How To Select Only Three Months Recored From Database
Hi guys,
Could anyone help me to write sql query to select only three months record from database
if the cur month is march then i need jan, feb and mar data , if it is jan, then i need nov , dec and jan data...how can i write sql query for this, i need this for creating reports
and also how cal i write queries based on days...mean to say that i need to select all records of particular month that falls on monday or tuesday...
Help me out.
Thanks in advance

View Replies !   View Related
Select Query To Get Last 6 Months Data
Hi,
I need to write a select query to get last six months data from a table , on monthly wise.Can anyone help me in writing this query
I am using sql server 2000.

eg: Jan...data
feb...data

TIA,
Ravi

View Replies !   View Related
Getdate() To Select Dates Less Than 15 Months
Hi,
I need help. I want to select dates less than 15 months, as i am new to sql server , can anyone advise me.

I tried this query but it gave me the dates that are greater than 15 months

Selec calendardate from table
where calendardate < getdate()-457

Thanks

View Replies !   View Related
T-SQL Scritp To Calculate The Last 6 Months Of Data In A Date Field.
Hi there,

if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.

Thanks very much,



Manny

View Replies !   View Related
Select Data From Table An Return All All Months
I have the following query:SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BYMonthUnfortunately it only returns rows for months that have data assignedto them.How can I tweak this so that months 1-12 are returned, and Hits = 0 formonths with no data in the base table?Thanks.

View Replies !   View Related
Selecting Data Within 1 Month, 6 Months, 12 Months


I have the following table



FeedBackType Date

test2positive 03/15/08

tes3negative 03/01/08

.. ....



in my page i need to select the number of negative/positive comments within the last



1 month, 6 months, 12 months



How can I accomplish that?
thanks



View Replies !   View Related
How To Select All Dates Upto Todays Date And Include The First Next Future Date
hello
how can i select all dates upto todays date and include the first next future date in a given data base

say todays date was the 01/06/2006 (MM,DD,YYYY)

below is a mock data base
id date (MM,DD,YYYY)
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
5 01/20/2006

i want to select all dates equal or less that 01/06/2006 and include the first next future date .. and in this case it would be 01/09/2006

so the results would return

1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006

View Replies !   View Related
Select A Date Minus Whatever Todays Date Is?
At the moment i have a query that is dependent on a date which is 42 days before whatever the date may be today.

The statement in my query I have to use is:

MailDate <= '2008-01-05'

I am wondering if i could make that statement automatically take off 42 days from todays date?

View Replies !   View Related
Date Select Query - Select Between Two Dates
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.

i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')

when i run this query i get this message

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15

please help
mustfa

View Replies !   View Related
Select Where Date = Todays Date
I have a table with a field 'service_date' held as datetime. I would like to select from the table where the service date is equal to today. The input is dd/mm/yy.

Cheers

Nathan

View Replies !   View Related
How To Select The Date Part Of A Date
I use smalldatetime for a datetime and i just display the date part
i'd like to compare the date part of the smalldatetime and the date i have
how can i do that ?
I know we can select the day,month or year ...
If you know a link where i could find different kinds of example about sql command it would be great to

thanks

View Replies !   View Related
Can't Select Date
hi, my DatePost field in the database formated as 2/15/2006 11:40:46 AM . i try to manually give a date (no) but it give me error. the error come from myReader!. help me  to correct, thanks no = "2152006"        Dim myConn As SqlConnection        Dim myCmd As SqlCommand = New SqlCommand        Dim myReader As SqlDataReader        Dim strSQL As String        myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))        strSQL = "SELECT Author FROM Booktbl WHERE cast(Date as datetime) ='" & no & "'"        myCmd.CommandText = strSQL        myConn.Open()        myCmd.Connection = myConn        myReader = myCmd.ExecuteReader        myReader.Read()        Author = myReader("Author")        myReader.Close()        myConn.Close()        lblShow.Text = Subject

View Replies !   View Related
SQL SELECT By A Date
I am trying to create a SELECT statement using a date. No data returns. However if I use the same Select Statement using ID or any other column it works fine. Is there a trick to using a date in a select statement?

Ex:
("Select * From Events WHERE Event_Date Like '%" & 12/21/2004 & "%'" , DBConn)

View Replies !   View Related
Select Between Date
I am trying to get al the rows from table1 where datetime is between 9:00AMyesterday and 9:00AM today if the time now is less than 9:00AM. Otherwise itshould return all where datetime>9:00 AM today.Is this possible as a query in sql2000?

View Replies !   View Related
Date Select
I have the following fields in a table

Cost StartDate EndDate
501/1/0412/31/04
751/1/0512/31/05
1001/1/0612/31/06

How do find how much something cost in a given date? e.g. 15/3/2005

Thanks in Advance.

View Replies !   View Related
Select Date Help
I want to select all orders from table orderdetails where the month of orderdate is = to November and year of orderdate = 2004. How would I code this? orderdate is a datetime sql field.

View Replies !   View Related
Select Date
Hello,

I'm trying to get all the records in my table that have a specific date. But I get no results.

The date column has the data type datetime and is stored like, for example, 11/01/2008 14:45:17 (dd/mm/yyyy)

So if I want to see al the records with date 11 januari 2008 I tried to do

SELECT *
FROM MyTable
WHERE date = '11/01/2008'

I also tried this

SELECT *
FROM MyTable
WHERE date LIKE '11/01/2008%'

But I get no results or no error message.

I checked the forum for an answer and found this solution

Select
*
from
NyTable
Where
MyDateColumn >= '20060114' and
MyDateColumn < '20060115'


but that doesn't work either.

Can someone please tell me what I'm doing wrong.

Thanks in advance
Wouter

View Replies !   View Related
How To Select Date Only
I am developing C# application and storing data in SQL 2005.

I have a datetime field and i want to select all records which has a specefic date regardless of time..

for example:

21/7/2008 12:40:32
21/7/2008 23:45:00

i want to select both because both has the same date

View Replies !   View Related
SELECT WHERE Date Is...
Hi All... I have a table that has a field called LogDateTime whose type is datetime.

How do I specify the WHERE clause if I want only those records that are say from the last three days? Such as WHERE (LogTimeDate < Today - 3 days)...

Thanks for any help!!! -- Curt



View Replies !   View Related
Sql Select To Include Date?
I have a table of parts and want to display some statistics from it.  The parts table has several fields in it, but the two that I need to use in my sql select queries are "PartUserID" and "DateUseBy", but I don't know how to use the "dateuseby" in the second example.Can someone help me out with #2?1. Display the total number of parts owned by a user:SQL Query:        SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId)"        OnSelecting="sqlPartCount_Selecting">        <SelectParameters>            <asp:Parameter Name="PartUserID" />        </SelectParameters>Code-behind:    protected void sqlPartCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e)    {        e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey;    }    2. Display the total number of parts owned by a user that are to be used in the current year. The table has a field "dateuseby" that should be used for the "Where" but I don't know how to get it.SQL Query:How to I include the "usebydate" so that it will use 2008 as the year ? I wrote the following query as an example of what I'm trying to get.        SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId, ZCPart.DateUseById = @PartUseById)"         onselecting="sqlPartYearCount_Selecting">        <SelectParameters>            <asp:Parameter Name="PartUserID" />            <asp:Parameter Name="DateUseByID" />        </SelectParameters>Code-behind:    protected void sqlPartYearCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e)    {        e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey;                // ? How do I change the date in the field to be in "2008" format and then put that in the sql query?        //e.Command.Parameters["@DateUseByID"].Value = currentYear;        //currentYear currentDateTime = DateTime.Now;    }

View Replies !   View Related
Date Select Question
Hello,
 How can i select all users that have birthday today (or a specified day)?
profile table:
username - dobuser1 - 1983-08-21 00:00:00user2 - 1986-04-15user3 - 1985-11-04user4 - 1983-04-02
SELECT username, dob FROM profile WHERE (???)
Is it possible to do this?somehow ignore the year and match the day and month
The hour/minute/seconds are always 00:00:00
Patrick

View Replies !   View Related
Select Date Record
    how to select record from the table where the data between a  range. example  between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks

View Replies !   View Related
Select Just The Row With The Earlier Date
Hello, I am having some problems with a select clause, the problem is the following:

I have a view with 3 columns: ID_PRODUCT, PRICE and INVOICE_DATE

In this view a can have the same ID_PRODUCT with differents PRICES in different DATES.
What I need to do is to select all 3 columns but showing just the rows with the higher DATE for each product, for example:
If I have the following data in my table:
ID_PRODUCT-------PRICE------INVOICE_DATE
2 1500 06/20/2004
9 1200 06/20/2004
9 1300 06/23/2004
10 800 06/22/2004

after making the selection I should get the following:

ID_PRODUCT-------PRICE------INVOICE_DATE
2 1500 06/20/2004
9 1300 06/23/2004
10 800 06/22/2004

that means that the oldest record for product 9 is eliminated, and just the earliest is shown..

May anyone help me, please..

Thank you very much

View Replies !   View Related
Select Statement Where DATE
I have a table with 67,000 records. One of the columns is a DATE datatype.I wanted to find every record newer than 2002 but I can figure out thecorrect SQL syntax.I tried:select * from my_table where LDATE >= 01/01/02But that doesn't return the correct information. Again, the LDATEcolumn is a Date/Time data format (per MS-Access).Could someone give me the correct syntax to pull this data?Thanks-Allen

View Replies !   View Related
Select From A Date Range
Hi guys,whilst working on a project which I thought was nearly complete I havecome across a problem which was some how over seen, which I am hopingone of you guys know how to resovle.Firstly here is the script to create the table and insert some sampledata:CREATE TABLE [dbo].[tbltemp999] ([Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[EventDate] [datetime] NOT NULL ,[EventID] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6005)The EventID 6006 represents a system shut down, and the EventID 6005represents a sytem start. Therefore from the above data you cans seethat the machine is not rebooted very often.My problem occurs when you select a date BETWEEN something where thereis no data. ie BETWEEN 2004-10-01 AND 2004-10-15 my desired resultfrom this should return 15 days of uptime but as there is no data itis returning nothing:(Can anyone work this one out?ThanksSunny

View Replies !   View Related
Date Select Problem Help..............
hi all
i've two tables containing date field
i want to select one date from the first table and also the next highest date from the second table
is there any provision for tht
thnks in advance
for eg

tbl1 tbl2

01/02/2004 15/01/2004

05/01/2004 02/02/2004

10/08/2004 03/02/2004

i want to get like this

01/02/2004 02/02/2004
05/01/200415/01/2004

jagans
thnks in advance

View Replies !   View Related
SELECT * WHERE [date] &> 100 Days
Hi !
for MS SQL 2000/2000, I need :

SELECT * FROM table1 WHERE table1.[date] > 100 days

how can i do that ?

thank you for helping

View Replies !   View Related
Conditional Date Select
I am trying to pull records that are at least 7.5 years old but I can't get my stored procedure to work right. Can someone please help me? My stored procedure is as follows:

SELECT max(inout.inout_date) as 'lastdate', inout.account_number, loans.short_name from inout
INNER JOIN loans ON inout.account_number=loans.account_number
WHERE datediff(mm, 'lastdate',getdate()) >=111
GROUP BY inout.account_number, loans.short_name


The error message that I'm getting is:
Syntax error converting datetime from character string.

(inout.inout_date is a datetime field)

View Replies !   View Related
Select Date A Week Ago
How to select a date a week ago?
Below is something i want

10/04/2006 ->09/27/2006

my code is like below:
SELECT CONVERT(CHAR(10),GETDATE(),101) - day(7)

but it cant work.

View Replies !   View Related
Select Latest Date Only
Hello,

If I have a TransactionTableT including with fields like this:
TransactionDateDT
QuantityIN
ItemID,
ItemCodeCH
ItemNameCH
PriceFL

Each time the user make an entry a new record is inserted.
How can I pull only the latest (TransactionDateDT) record for each item. I've tried MAX(TransactionDateDT), but how can I get ItemCodeCH, ItemNameCH and PriceFL.

Basically I need to create a report with the latest price for each item.

Any feedback welcome!

Edi

View Replies !   View Related
How Tot Select The Most Recent Date
I have 2 tables:
1) Item table (No_, Description)
2) Sales Price HAGtable (Item No_, Sales Price HAG, Starting Date)

I want to have a report within from every itemthe most recentsales price. But, it is possible that a unique item has more than 1 sales price in the 'Sales Price table' (if it has more than one Starting Date). I want to report the sales price with the most recent date.

I've made this statement:
SELECT [DatabaseName$Item].No_, [DatabaseName$Item].Description,
[DatabaseName$Sales Price HAG].[Unit Price]
FROM [DatabaseName$Item] INNER JOIN
[DatabaseName$Sales Price HAG] ON
[DatabaseName$Item].No_ = [DatabaseName$Sales Price HAG].[Item No_]
WHERE ([DatabaseName$Sales Price HAG].[Starting Date] IN
(SELECT MAX([Starting Date]) AS EXPR1
FROM [DatabaseName$Sales Price HAG] AS [DatabaseName$Sales Price HAG_1]
GROUP BY [Item No_]))



After running I still get from some items more than 1 price...... What went wrong?

Thx for help!

View Replies !   View Related
Select Max Date Row For Each Year
Hi !

I've got a list like this one:










uniqueID
technicianID
StatusID
Date

1
1133
2
01-01-2003

2
1133
2
01-03-2003

3
1133
2
01-01-2004

4
1133
3
01-08-2004

5
1133
3
01-02-2007

6
1133
4
01-04-2007


i need to select only the last rows of each year :





2
1133
2
01-03-2003




4
1133
3
01-08-2004




6
1133
4
01-04-2007

How can i achieve that , i have tried with max, but it does not retrieve all the fields.

thanks




View Replies !   View Related
Select Event Where Date &>= Today
 Hello. I have an "Events" table with a datetime column containing dates (the time is unnecessary). I want to select an upcoming event (one post) from the table where the date is either today or the day nearest to today.
Dim today As Date = Date.Now 
I have declared a 'today' variable but I don't know how to use it in
the SQL query. Will I have to convert the today variable to something before using it?
Should a Label be involved? The event will be displayed in DetailsView.

  

View Replies !   View Related
Select Date From A Datetime Column
Well yeah as the topic says, this is my sql question right now;string date = "2006-11-12"; string sql = "SELECT * FROM spell_of_work WHERE date='{0}'",date);problem is that my date column in the table is datetime and not a date so.. error! i dont know if RLIKE or LIKE can help, wating for a reply!herman 

View Replies !   View Related
Stored Procedure - Sql Date Select
Having a little trouble getting this one to work.
I have a stored procedure that selects items from the table...
SELECT fldDate, shortTitle, longTitle, Email,  POC, News, guidFROM tblNews
I need to change it so it selects only the year needed.  For instance, only items from 2004.
SELECT fldDate, shortTitle, longTitle, Email,  POC, News, guidFROM tblNews WHERE fldDate = @myParameter
-Say myParameter = 2004 or whatever year is needed
The table field fldDate is a datatime field that has date and time stored and I can put any parameter into the stored procedure.
How can I be sure to only get a certain year?
Thanks,
Zath

View Replies !   View Related
URGENT - Select Distinct Date
Hi, I have a query as follows:select transactionDate, (select count(transID) from ......)I am having a problem which is that dates are not shown uniquely.For example,oct 1, 2005 00:12:00 - 12oct 1, 2005 01:30:00 - 12oct 1, 2005 01:13:00 - 12I want only to have:oct 1, 2005 01:13:00 - 12The select is working on time and not on a date, can you help me please?thank you.

View Replies !   View Related
Select Date Only Existing In One Table From 3
Hi the subject matter pretty much sums up what i need, a piece of sql code that queries 3 or more tables and selects only those records that have a date field that only occurs in one of the tables. So for example if one table has 20 records with a date of 12/12/04 and the other 2 tables do not have any records with a date of 12/12/04 then i only want to select those records with a date of 12/12/04.

Thanks in advance for the help

View Replies !   View Related
Select Statement Based On A Date
i am not getting a result back when i run the query below.

select * from users where DateCreated = '2004-02-19'

so i went into the table and looked at the record. for DateCreated field i have both date and time. ex: 2004-02-19 08:40:00

how can i select this record with out using the time in the select statement. what i want to see is how many users signed up for a day. any ideas?

View Replies !   View Related
Select Statement To Show Last Amt And Date
I'm trying to write a select statement that will show me the total payments, last payment date, and last payment amount for each client. I get results but it is all payments. Can anyone help me with this?

Thank you,

Here is what I have tried:

SELECT dbo.tblClients.Client_ID, Sum(dbo.tblPaymentReceipts.[Amount Paid]) AS SumOfAmtPaid, MAX(dbo.tblPaymentReceipts.[Date]) AS LastPaymentDate, dbo.tblPaymentReceipts.[Amount Paid] INTO #temp_UNPaymentsA
FROM dbo.tblPayments INNER JOIN dbo.tblPaymentReceipts ON dbo.tblPayment.Pay_ID = dbo.tblPaymentReceipts.Pay_ID
WHERE (dbo.tblPaymentReceipts.[Date] BETWEEN '1/1/2001' AND '12/31/2003')
GROUP BY dbo.tblPayments.Pay_ID, dbo.tblPaymentReceipts.[Amount Paid]

Select * FROM #temp_UNPaymentsA
GROUP BY Client_ID, SumOfAmtPaid, LastPaymentDate, [Amount Paid]
HAVING SUM(SumOfAmtPaid) BETWEEN 0 AND 1000

View Replies !   View Related
Max Date Select Stmt Problem
Hello Everybody,I have a problem, with select stmt:SELECT TOP 15 *FROM oaVIEW_MainData AS TOP_VIEW,oaLanguageData_TAB AS RwQualifierJoin with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin.text_id = c_cfgRegPointAND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB ASRwQualifierJoin1 with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin1.text_id = c_cfgRegPointAND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)order by c_dateTime descPlease consider:- top doesn't metter, if I will use one or 10000 result is always thesame.- oaVIEW_MainData, is a view on major big table, holding lot of recordsjoinden with small table containing configuration data, over left outerjoin; both tables are with nolock option,- quersy supose to return last record from major table/view, in giventime, additionaly, with other where conditions (like in this case withtext),- on major table, are indexes which one is on id field (not used inthis query at all), which is a pk clustered, and other is on dateEvt(c_dateTime) which is a desc index with fill level 90%- table has also other indexes, on three different fields, one oftheses is dsmIdent,Now, if I'm using max(id) works very fast, and ok for me, but theproblem is, I should not use id, because might be, that the recordswill be written in the table with random order, so the only one sayingwhich is newest, will be dateEvt.Using dateEvt as max(), dramaticly slows query, so I'm acctualy unableto get result. What is much more funny, server is totaly busy with thisquery, and it's procesor jumps on 100%.Now, because the query is builded dynamicly, by a user selections,that's why we decided on such a parser ... problem is, it is notworking :(Can I change index on dateEvt somehow, to sped this up?Maybe construct query somehow different, to get this over max() date?Please helpMatik

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved