|
Hi,
I have mysql database and employee details table is there with the column joining date.
i need to write a query in mysql in such a way that i should compare joining date with today date since i need to display employee details who are going to complete exactly 1 yr, 2 yr , 3yr ... and so on.
For ex:
Joining_Date Current_Date Years_Completed
2010-05-26 2014-05-26 4 years
2000-05-26 2014-05-26 14 years
2010-04-26 2014-05-26 4 years this row should not come since one month is less
only for todays date they should exactly complete years
How to achieve this. If anybody knows, please reply me.
Thanks in advance.
|
|
|
|
|
What happens with the following joining dates if todays date is 2014-05-26
1. 2012-05-25
2. 2012-05-26
3. 2012-05-27
4. 2013-05-27
|
|
|
|
|
if todays date is 2014-05-26 then
1. 2012-05-25 if u consider 2 years means 730 (365 X 2) days will come but in this 731 that 2 years 1 day so we should display this, it should complete exactly year
2. 2012-05-26 i think this is coming 730 days means 2 years exactly we can display
3. 2012-05-27 in this 1 day required to complete exactly 2 years now it is 729 days
4. 2013-05-27 in this 364 is coming it should be 365 to complete a year so we should display this record
How to achieve this. Please reply me.
|
|
|
|
|
Don't know MySQL but you should be able to break the datetime object into day/month/year/time components. Select from the table where the day = day and the month = month, ignoring the year and time components.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You have to use date formatting in year
|
|
|
|
|
Hi,
Try below :
DECLARE @JoiningDate DATE
SET @JoiningDate = '10/10/2013'
IF (YEAR(GETDATE()) > YEAR(@JoiningDate))
BEGIN
IF (DAY(@JoiningDate) = DAY(GETDATE()) AND DAY(@JoiningDate) = DAY(GETDATE()))
BEGIN
PRINT CONVERT(NVARCHAR,(YEAR(GETDATE()) - YEAR(@JoiningDate))) + ' Year(s)'
END
END
|
|
|
|
|
Hi gurus!
I have created a report in Access2010 and seems like the data alignment in report view is not what I want. My current view is like the pix in the following URL. Is it possible to have those tasks shown just below the headers (months)?
I tried to merge those cells for display or even those criteria like Does Not Equal blank but both are not what I want. Or should I better modify my query?
my current tabular view
There are two tables for such layout.
tables involved:
Tasks: TaskTitle, ProjectID, StartDate, DueDate
Projects: ProjectName, ProjectID
key: ProjectID (number)
For the query I am using, it is a bit "weird" and I wonder that is the main reason I can't get those tasks aligned to top. However, some of the tasks may have StartDate and/or DueDate empty. A big thank you in advance for your help since I seldom work with MS Access. Thanks in advance!!!
TRANSFORM [Tasks].[TaskTitle] AS [the value]
SELECT [Projects].[ProjectName]
FROM Projects LEFT JOIN Tasks ON [Projects].[ID] =[Tasks].[ProjectID]
GROUP BY [Projects].[ProjectName], [Tasks].[TaskTitle]
ORDER BY [Projects].[ProjectName]
PIVOT Format ([Tasks].[StartDate], "MMM-YYYY");
|
|
|
|
|
Dear members,
I have recently installed SQL Server 2008 Enterprise Version. I have a database built in SQL SERVER 2000 which I attached in 2008.
I have a stored procedure which calculates the Inventory Stock.
In some cases the sp deducts qty twice when call from front end (VB 6.0) whereas when I run the sp in SQL Server it returns correct result
It was running perfectly in SQL Server 2000 from VB 6.0
What could be the cause of this abnormal behavior?
Note: The Compatibility Level of the database is set on SQL Server 2000 (80)
Waiting for your kind replies.
Thanks & Regards,
Syed Adnan Shah
|
|
|
|
|
There is a bug in your code, but no one here can guess what your code is doing.
|
|
|
|
|
Dear Richard MacCutchan
I have written in my post that it is running perfectly through code (VB 6) using SQL Server 2000.
The problem occurred when i attached 2000 database in 2008 Enterprise.
|
|
|
|
|
You can write anything you like, it proves nothing. And, as I said before, no one here has the remotest chance of guessing what your code is doing.
|
|
|
|
|
Turn on Profiler and log what is happening when you call the stored procedure directly versus when you call it from VB 6.
|
|
|
|
|
Thanks for your reply
When I call The Stored Procedure directly from Sql Server it adds or
subtracts stock qty properly, but when i run it through vb6 code it adds or
subtracts qty twice. But its not in every execution of SP.
I m running my software for 3 to 4 years it never happened like this in SQL
Server 2000
|
|
|
|
|
What Tim is trying to suggest
1. Turn on SQL Profiler and start it running against your database
2. execute the stored procedure from the VB application
3. stop the profiler
4. in the results from the profiler you can see what was executed and the values that were passed to the stored procedure
http://technet.microsoft.com/en-us/library/ms181091(v=sql.105).aspx[^]
you need to be able to prove if it the database at fault or the stored procedure.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Thank you Simon for explaining me what Tim has suggested. Let me do what you both have said and let you know.
Thanks
|
|
|
|
|
jugnu_ad wrote: What could be the cause of this abnormal behavior?
VB6.
Which existed waaaay before Sql Server 2008. Chances are that most of the code isn't type-safe but late-bound, and VB may be doing all kinds of magic on the background to make it work.
Did I mention that VB.NET is available for free?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Sounds like this may be the issue that finally gets him to update/rewrite his app into a current language. Now there will be a whole lot of pressure while he could have started this a decade ago (so speaks the guy still coding in Silverlight  ).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
While VB 6 may have it issues, I have written and used in production many applications written in VB 6 pulling data from SQL Server without issue. If there is a problem, I suspect it is with the coding, not the underlying foundations.
|
|
|
|
|
Tim Carmichael wrote: While VB 6 may have it issues, I have written and used in production many applications written in VB 6 pulling data from SQL Server without issue.
Ditto. I never claimed that VB6 has issues with SQL Server - merely that it existed before Sql 2008. VB6 does not know about Sql2k8, and I doubt that new released Sql versions are tested for backward compatibility for a discontinued product.
Tim Carmichael wrote: I suspect it is with the coding, not the underlying foundations.
I agree with the first part of this statement, and explained how the underlying foundation can cause this.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
When you state that something isn't working, you need to show us what that 'something' is. I mean you haven't included your SP code here without which we will not be able to figure out what is wrong.
|
|
|
|
|
hi to all
i want to update a column from start point for example
i want to update from 10 .how can i do this
columns mycolumn
Row1 10
Row2 11
Row3 12
. .
. .
. .
|
|
|
|
|
I'm not sure if I understood you correctly, but you can use the UPDATE statement with a WHERE Clause to control which rows are updated.
UPDATE MyTable SET Column1 = 'Value1' WHERE mycolumn = 10
UPDATE MyTable SET Column1 = 'Value1' WHERE mycolumn > 10
UPDATE MyTable SET Column1 = 'Value1' WHERE mycolumn BETWEEN 10 AND 12
UPDATE MyTable SET Column1 = 'Value1' WHERE mycolumn IN (10, 12, 15, 30)
|
|
|
|
|
i want to update mycolumn with 10,11,12,.....
assume that my table have 100 row
then mycolumn with 10,11,...,110
thanks for answer
|
|
|
|
|
Still not clear. Update your question with sample data(like BEFORE and AFTER format) with clear explanation. Take your time.
before
A B
----
1 1
2 2 after
A B
----
2 2
3 3
|
|
|
|
|
befor
A B
----
1 NULL
2 NULL
3 NULL
4 NULL
after
A B
----
1 10
2 11
3 12
4 13
thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
