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()
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
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
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
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.
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
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
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)
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.
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?
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
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; }
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:
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
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.
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.
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
I need this query to pull just the last scan date, i.e. if a scan ran on 3/7/2012 I need just the records (there may be few or many) that pertain to that date and no other dates. When I run this query I am still getting all of the results (i.e. scan dates of 3/7/2012, 3/5/2012, 3/1/2012, etc)
I just need the most recent date, even if there are many records for that date (i.e. 10 IP addresses that were all scanned on 3/5/2012)
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
I have 2 tables: 1) Item table (No_, Description) 2) Sales Price HAG table (Item No_, Sales Price HAG, Starting Date)
I want to have a report within from every item the most recent sales 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?
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
