Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-09-21 : 07:15:53
|
| raja writes "Hi I want to calculate the experience of the employees which was in the excel sheet i export the data to the sqlserver using the query select * into ManPowerCommon FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Book.xls;HDR=YES', 'SELECT * FROM [Common$]') in the excel sheet d date in d format 5th january 2005 first I replace d string th or nd or rd or st by space then i apply d query select datediff(year,replace(F9,substring(F9,patindex('%nd%',F9)+ patindex('%rd%',F9)+ patindex('%th%',F9)+ patindex('%st%',F9),2),' '),convert(varchar(20),getdate()))as "ExpInHC(in Years)", datediff(month,replace(F9,substring(F9,patindex('%nd%',F9)+ patindex('%rd%',F9)+ patindex('%th%',F9)+ patindex('%st%',F9),2),' '),convert(varchar(20),getdate()))"ExpInHC(in Month)" from ManPowerCommon where F9 != 'Date of Joining HC'and F9 != 'Null' and patindex('%ust_%',f9)<=3it will work for otherdates except the date having d month name"August" because it will search d path index for d day and also st which is in august add the both indexpositions but it is not displayed. can u suggest me how to take only one index except "st" in august. Waiting for suggestion .... Thanks Rajashekar." |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-21 : 15:26:10
|
| Either replace "st " with space, instead of replacing just "st", or assume that "august" has become "augu" and treat that as a "valid" month, or re-replace "augu" with "august" AFTER the "st" replace with space so that it becomes "august" again.Kristen |
 |
|
|
|
|
|
|
|
How to calculate the date difference