I think this is a simple question but I am just not sure how to do it right, as a newbie.
All I want to do is generate a report of company employees grouped by their date of hire.
SELECT DateOfHire, FirstName, LastName from Empls
ORDER BY DateOfHire
The only problem is that the DateOfHire displays as, e.g., '01/03/2004 12:00:00' which is not what I want. I SIMPLY WANT TO OMIT THE TIME PART but don't know quite how to say this. I tried several variations along this line but it causes scoping errors...
SELECT CONVERT(Varchar(10), DateOfHire, 101) AS DOHDateOnly, FirstName, LastName, State
FROM EMPL
GROUP BY DOHDateOnly
but this gives error
"The value expressions for the textbox ‘DateOfHire’ refers to the field ‘DOHDateOnly’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Which makes sense, but I don't know HOW to say the right thing to get what I want.
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 want to use the date part of the function getdate in my sql query. the getdate function return the current date and time. i want only the date part. how can i do it.
Ok, so I have a Select statement that looks like this:
Select * from FilteredOpportunity where (OpportunityID LIKE @opportunityid) and (Name LIKE @name) and (CustomerIDName LIKE @customeridname) and isNull(EstimatedCloseDate, '1900-01-01') LIKE @estimatedclosedate and isNull(Description, '') LIKE @description and isNull(AccountIdName, '') LIKE @AccountIDName and isNull(ACCU_OppTypeName, '') LIKE @ACCU_OppTypeName and isNull(SalesStageCodeName, '') LIKE @SalesStageCodeName and isNull(ACCU_CustomerTypeName, '') LIKE @ACCU_CustomerTypeName and (CreatedByName LIKE @CreatedByName) and (CreatedOn LIKE @CreatedOn) and isNull(ACCU_OppSourceName, '') LIKE @ACCU_OppSourceName and (StateCodeName LIKE @StateCodeName) and (StatusCodeName LIKE @StatusCodeName)
This chunk of code is used in an XML file which, once this XML is imported (using sharepoint by the way if you haven't noticed), I can then search through tables with different filters. Well, my date filter isn't working the same way as my other ones. I have a couple different date filters, but none of them work.
Before I ask my question, I only pass in one variable at a time through my code.... the rest of the variables get a default value passed to them if the user doesn't pass it. That value is simply '%'. Anyways, so the different ways a user can type in a date is by typing in the whole date, part of the date starting with the beginning of it, typing in the end of the date, or typing something within the date. For a string it's like 'Example', 'Exam%', '%ple', and '%amp% respectively. How do I accomplish this with a date?
Is there any other way which is more simpler(shorter) to get the datepart (I don't want the time part ) of Datetime than this ?Convert(nvarchar, DATEPART(dd,[Date]) ) + '/' + Convert(nvarchar,DATEPART(mm,[Date])) + '/' + Convert(nvarchar, DATEPART(yy,[Date])) As[Date]
We have some seasonal products that we would like to turn on and off automatically on our website. To accomplish this, we've added StartDate and EndDate fields to the product table. See simplified table: --------------Simple Table:--------------ID int identity (PK)Value varchar(50)StartDate datetimeEndDate datetime Normally I could just do something like SELECT ID, Value FROM PRODUCTS WHERE StartDate <= @MyDate AND EndDate >= @MyDate The catch is that we want to ignore the year part of the dates so that we don't have to go back through and update all the records every year. I'm pretty sure I've done this in the past, but I'm having a brain fart right now and can't remember how. One of the issues is that the start month could be > the end month (e.g. - October 15 - January 1). Using code in the application I could work around this (see below), but I would like to handle this in SQL if possible so I don't have to return more records than are needed and weed out the bad records. 'dtStart and dtEnd are Date Objects, StartDate and EndDate are strings containing the MM/DD partIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(myDate) < 0 Then dtEnd = Date.Parse(EndDate & "/" & (myDate.Year + 1))ElseIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(objDate) > 0 Then dtStart = Date.Parse(StartDate & "/" & (myDate.Year - 1))End If I appreciate any help you can provide. Thanks,Sam
I would like to know the best way of returning just the Date part (dd/mm/yyyy) of a DateTime field using T-SQL. I know that some methods mean that you lose the indexing if the field is indexed. Which is the best way to do this in T-SQL whilst preserving the indexing? I'm using SQL Server 2005.
I wrote the query like this "select datepart(year, Msg_Time) from C20_Messages" This query result is:- 2006 2006 2006 2005 2005 2005 2005 Here I want to get only the year part without repeating. I mean 2005 2006
I am wondering has anybody ever created a Matrix in a report grouped by Day Part (10-2 Morn, 2-6 Aft, 6-10 Eve etc) and Date?
I would like to see a report with an output such as
13/02/2006 14/02/2006 Total Morn Aft Eve Morn Aft Eve Mr A 2 4 5 2 6 2 21 Miss B 8 8 1 1 4 5 27
I have a DB table which records the datetime each time a viewer changes TV channel. The report is a summary by user of each channel change By Day Part with Each Date
I can do this easily enough by just grouping on Date but now require another level of detail.
I'm trying to find if any part of a date range in my table between orig_start_rent and stop_rent falls within a period I specify in two variables: startPeriod and endPeriod
For example if I specify 2013-11-01 as startPeriod and 2013-11-30 as endPeriod, then if any part of the date range between orig_start_rent and stop_rent(stop-rent can be null if hire is open) falls within that period, I want that to be picked up and assigned the value of 1 in my case statement for OnHire. My code is not picking everything up however - using the example above, a record with orig_start_rent of 2013-05-23 and stop_rent of 2013-11-18 is being assigned 0 when it should be 1. My code here:
declare @startPeriod as smalldatetime declare @endPeriod as smalldatetime set @startPeriod = '2013-11-01' set @endPeriod = '2013-11-30'; select dticket, orig_start_rent, stop_rent, case when orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod then 1 when orig_start_rent < @endPeriod and stop_rent is null then 1 else 0 end [OnHire] from deltickitem
from this, circdate being a datetime field:SQLQuery = "select distinct circdate from circdata order by circdate"I need the distinct date portion excluding the time part.this has come about when I discoveredI am inserting and updating some datetime values with the same value,but for some reason, the values are always off by a few seconds. I seta variable called SetNow assigned to NOW and then set the datetimefields to this SetNow variable. Then when I collect the distinct datetime I am assuming they will have the same values recorded incircdate, but no, they are off by several seconds. Makes no sense to meat all. I tried renaming the variable several times but it makes nodifference at all.any help appreciated, thanks.
OK, the thread's name is not very self explanatory so here I go:
The fact I'm working on a simple APP where I store the date of a transaction. I use in my Stored Procedure the getdate() as the default (and untouchable) value for the transaction date
The problem: These transactions are made all day long and there are transctions made on the morning and afternoon. Now, when I make a report of the transactions for one specific range of days, all the transactions made in the morning of the start date are ignored completely.
Say I have these transactions TCode TType TDate 45 REQ 03/09/2007 08:20:16 a.m. 46 REQ 03/09/2007 11:59:43 a.m. 47 REQ 03/09/2007 12:01:30 p.m. 48 REQ 03/09/2007 06:01:49 p.m.
and I make this query SELECT * FROM <Table Name> WHERE TDate BETWEEN <Start Date> AND <End Date> (where <Start Date> && <End Date> are 03/09/2007 selected from a DateTimePicker control)
I only get these results 47 REQ 03/09/2007 12:01:30 p.m. 48 REQ 03/09/2007 06:01:49 p.m.
if I make a cast as integer I get these results: TCode TDate TCast 46 2007-09-03 11:59:43.000 39326 47 2007-09-03 12:01:30.000 39327
so my question is Why are the Morning Transactions from the start date ignored (or interpreted as part of previous day transactions) ?
I need to set only the date part of tblEventStaffRequired.StartTime to tblEvents.EventDate while maintaining the time part in tblEventStaffRequired.StartTime.
UPDATE tblEventStaffRequired SET StartTime = <expression here> FROM tblEvents INNER JOIN tblEventStaffRequired ON tblEvents.ID = tblEventStaffRequired.EventID
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site. I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part. While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as "Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel. What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config? I guess while I use Part 1 to connect, maybe it will be cancel exclusive method of the database ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time!
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
In a recent post, I've read that Jet Provider is only provided with 32-bit.
I wonder, is there any problem if you run a SSIS package 32-bit which read from Access and then call another SSIS package 64 bit?? We've got quite 2000 dts which pulling data from MDB and within a time it'll disappear in favour 2005 environment .
Another question, what sort of Providers don't offer us 64-bit versions???
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
Getting back way too many records with this on - over 4500 and should only be about 350..... Here's the sql that's not working right: SELECT c.Name, a.ID, a.Description, s.Status, b.Location, a.scheduleBegin, a.scheduleEnd from tblEquipments a inner join tblEquipments_Group U on u.categoryID = a.EquipmentType inner join tblStatus S on a.status = S.statusID and u.subCategoryID = @type inner join tblLocation b on a.status = b.locationID inner join tblSubCategories c on u.subCategoryID = @type and c.Parent = @type The table tblSubCategories has a field Name that I need. tblSubCategories has a field, Parent, that will = @type See this post for the other table fields if needed. http://forums.asp.net/thread/1489880.aspx If I omit the last inner join, all is ok, but I need that last bit of info - the c.Name. Suggestions? Thanks, Zath
ok, following up on my previous post that I marked as answered a little premature. The query below works fine in sql studio: SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate FROM guest but when I try to use it in a c# code behind file: comm = new SqlCommand("SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate FROM guest WHERE id = @id", conn); it bombs??
