|
your query is not clear.
however the result of year and mth from date_hired can be obtained (as per your example) ....
select date_hired, datediff(yy, date_hired, getdate()) AS 'Year', datediff(m, date_hired, getdate()) AS 'Month'
now it will return months as 23
Regards
KP
|
|
|
|
|
Use the DatePart function.
SG
|
|
|
|
|
Try to understand your question... hope this is what you want:
SELECT [date_hired], [Year] = DATEDIFF(m, [date_hired], GETDATE())/ 12, [Month] = DATEDIFF(m, [date_hired], GETDATE()) % 12
reference --> http://www.oin1.com/Technical/SQL/DateVsCurrent.htm[^]
Eliz.K
|
|
|
|
|
Hello everybody
I have a table "Employee" which contains 20 records. i want to select records from 10 to 15. how can I select?
Regards, Qaiser Nadeem
|
|
|
|
|
You need to make a ID field in Employee table from 1 to 20
Then do this
Select * from tblEmployee where ID in [10,15]
It seem to be a solution or an answer.
|
|
|
|
|
select * from Employee<br />
where [id] >= 10 and id <=15
I Love SQL
|
|
|
|
|
If you only want 10 of the 20 records you can do
select top 10 * from yourtable
Ben
|
|
|
|
|
in sql server there is a new feature which will help you to send e-mails via the database .do you know exactly how to do this .
eg:
if(time period expires)
send an e-mail;
|
|
|
|
|
|
Hello,
VS 2005
I have a datagridview that is bound to a bindingsource.
I am trying to merge this into another data table that has the same schema.
I have filled the dgv with the following code:
The reason i have used a dataview (dv) is so that when the customer selects from the combo box in the dgv, it doesn't automatically filter the rows.
'Fill the pending orders that belong to the order ID<br />
Private Sub FillPendingOrders()<br />
Try<br />
Me.TA_OrderDetails_dsCodeRed1.Fill(Me.DsAddComponetAndEquipment.OrderDetails)<br />
<br />
dv = New DataView(Me.DsAddComponetAndEquipment.OrderDetails)<br />
dv.RowFilter = String.Format("Status = '{0}' AND OrderID = '{1}'", "Ordered", CInt(Me.cboPendingOrders.SelectedValue))<br />
Me.dgvPendingOrders.AutoGenerateColumns = False<br />
Me.bsOrderDetailsPending.DataSource = dv.ToTable()<br />
Me.dgvPendingOrders.DataSource = Me.bsOrderDetailsPending<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
End Sub
However, when i update the datagridview by entering some number into the cells, and click the save button. I have checked the database to see if the value has not been updated, and it hasn't.
This is how i am updating:
Me.bsOrderDetailsPending.EndEdit()<br />
Me.DsAddComponetAndEquipment.OrderDetails.Merge(DirectCast(Me.bsOrderDetailsPending.DataSource, DataTable), false)<br />
'Just to check to see if the value is displayed in the source column. This displayed what I value i put into the source cell ok.<br />
source = DirectCast(Me.bsOrderDetailsPending.Current, DataRowView)("Source").ToString()<br />
<br />
Me.TA_OrderDetails_dsCodeRed1.Update(Me.DsAddComponetAndEquipment.OrderDetails)
Many thanks for suggestions,
Steve
|
|
|
|
|
Hi, I got the following from a tutorial on using store procedure for custom paging and I'd like to have few questions answered.
CREATE PROCEDURE uspPaging
@nStartValue INT,
@nEndValue INT
AS
SET NOCOUNT ON
DECLARE @tblTempData TABLE
(
nID INT IDENTITY,
EmployeeID INT,
LastName VARCHAR(50),
FirstName VARCHAR(50),
SupervisorID NCHAR(5)
)
INSERT INTO @tblTempData
(
EmployeeID,
LastName,
FirstName,
SupervisorID
)
SELECT
EmployeeID,
LastName,
FirstName,
ReportsTo
FROM Employees
ORDER BY
EmployeeID,
FirstName
SELECT EmployeeID,
LastName,
FirstName,
SupervisorID
FROM @tblTempData
WHERE nID BETWEEN @nStartValue AND @nEndValue
ORDER BY
nID ASC
What I don't understand is how does the computer know what the values of @StartValue and @EndValue are if they are not initialized. In this tuturial and another one I was reading, the @ symbols are used in naming the variables. Is it a must to use the @ symbol as the prefix of the variable name? Also does Between mean that if StartValue = 1 and EndValue = 5, then only EmplpoyeeID = 2 through 4 will be returned? Thank you in advance for your help.
-- modified at 23:57 Wednesday 1st August, 2007
|
|
|
|
|
ASPnoob wrote: how does the computer know what the values of @StartValue and @EndValue are
They are the variables that store the parameters that you pass to your stored procedure when you call it. You need to pass the values to the stored procedure.
ASPnoob wrote: does Between mean that if StartValue = 1 and EndValue = 5, then only EmplpoyeeID = 2 through 4 will be returned?
No, all values from 1 to 5 inclusive will be returned.
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
Hi, thank you so much for your response, but I have one more question that I really need answered. Is 2 the number of result per page that will be displayed, if not how do I make it display the desired number of results per page? Thanks again for sharing your knowledge.
|
|
|
|
|
The number of results it will return is dependent on the difference between the start and end values you pass it.
eg: if you pass the stored procedure a start value of 10 and an end value of 25, it will return 16 records (end value - start value + 1)
Are you understanding why the stored procedure is doing what it's doing?
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
Hi, please correct me if I'm wrong. I think that @nStartValue and @nEndValues are used as reference points. You Display the file in the temporary table starting with the one whose ID is equal to @nStartValue and stop at the one whose ID is equal to @nEndValue. So if 50 is @nStartValue and @nEndValue is 60, and the total number of files retrieved is 100. Only 10 files will be displayed starting with the one whose ID is 50 and ending with the one whose ID is 60. The rest will be discarded, and if that is the case then it will not solve my problem. Does that mean that if I want to display every file that is retrieved, I would have to know ahead of time how many files will be retrieved by my query? Thank you for your time.
-- modified at 3:19 Thursday 2nd August, 2007
|
|
|
|
|
Yes, your description is fairly accurate. The point of the stored procedure it would seem is to set up a paging system, so that the N records between X and Y are returned. eg: the first page of records might return 1-10, the second page 11-20 etc.
That's pretty much would it would be useful for. What are you trying to achieve?
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
cn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
this code for Access how i can transfer it to SQL????????
hi
|
|
|
|
|
1 - write sensible subjects for your posts
2 - this is a ASP.NET/VB.NET question, it doesn't actually relate to SQL.
3 - SQL Server and SQL are two different things. I assume you want to transfer to SQL Server ?
3 - just change your connection string to one that connects to SQL Server, in your web.config file.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Set the connection string to a SQL connection string in your app.config. http://www.connectionstrings.com[^] is a great site for reference if you haven't already found it 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi, how do I convert select statement and the connection variable in the following line into a stored procedure.
Dim AD as New OdbcDAtaAdapter("SELECT ResumeID, Resumes From Professions", myConnection)
Thank you in advance for your help.
-- modified at 17:35 Wednesday 1st August, 2007
|
|
|
|
|
CREATE PROCEDURE procname ( parameters ) AS batch of statements
|
|
|
|
|
See the below C# snippet that's itterating over a SqlDataReader:
while (reader.Read())
{
c.ID = reader.GetInt32(0);
c.Category.ID = reader.GetInt32(1);
c.Subject = reader.GetString(2).Trim();
c.Body = reader.GetString(3).Trim();
c.SourceVideoClip = reader.GetString(4).Trim();
c.StillImage = reader.GetString(5).Trim();
c.Notes = reader.GetString(6).Trim();
c.UploadTime = reader.GetDateTime(7);
c.AdID = reader.GetInt32(8);
c.ValidAfter = reader.GetDateTime(9);
c.InvalidAfter = reader.GetDateTime(10);
c.BillingCode = reader.GetInt32(11);
c.Name= reader.GetString(12).Trim();
c.Birth = reader.GetDateTime(13);
c.Category.Name = reader.GetString(14);
}
Everything runs fine until it gets to reader.GetString(4).Trim();, which is null, so it throws an SqlNullValueException exception.
How can I gracefully handle these null values. It seems GetString & GetInt just throw exceptions when they encounter null values. I guess the solution I'm looking for would return null or 0 for a db null value.
How am I supposed to deal with this. Do I have to put if statements around each GetXX to check if it's null?
/\ |_ E X E GG
|
|
|
|
|
I tend to use a set of wrappers to handle these. For instance:
public static string GetValue(IDataReader reader, string columnName)
{
int pos = reader.GetOrdinal(columnName);
if (reader.IsDBNull(pos))
return string.Empty;
return reader.GetString(columnName).Trim();
}This would make your code easier to maintain because you could do:
c.Notes = Utility.GetValue(reader, "Notes");
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Another way to do this is to use a generic function:
public static T GetData<T>(IDataReader reader, string column)
{
if (reader.IsDBNull(reader.GetOrdinal(column)))
return default(T);
return (T)reader[reader.GetOrdinal(column)];
}Then, call it using
c.Category.Name = Utility.GetData<string>(reader, "categoryName");
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I like this!
/\ |_ E X E GG
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
