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 |
|
saurabh122
Starting Member
16 Posts |
 Posted - 2009-01-19 : 15:02:26
|
| Hi,I need to convert all the dates which are stored in julian format in the GMT date format.We already have a function for that(dbo.fn_adjusted_date).I wanted to know if it is possible to replace all occurrences of those dates with some function in SQL Server. An example of the data string in which the conversion needs to take place is as follows:1227208483kkk.aaa@cc.comApproved1227208484Remedy Application ServiceProcess ApprovedI was able to convert the first 10 characters to GMT date format using charindex/patindex and replace function. Below is the code used. select replace(substring(Approval_Audit_Trail,PATINDEX('12%',Approval_Audit_Trail),datalength(Approval_Audit_Trail)), substring(Approval_Audit_Trail,PATINDEX('1%',Approval_Audit_Trail), 10 ), dbo.fn_adjusted_date(substring(Approval_Audit_Trail,PATINDEX('1%',Approval_Audit_Trail), 10 )) ) from CHG_ChangeAPDetailSignatureCan any one tell me how to replace all the occurences using a single select statement? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 02:09:56
|
| you mean change dates in various fields? then you need to apply function on each |
 |
|
|
saurabh122
Starting Member
16 Posts |
Posted - 2009-01-20 : 03:05:10
|
| i already have a function to change the date format...the only problem is the dates are appearing more than once in a string....and when i use patindex and other mentioned functions, the only first instance is converted.....example:1227208483kkk.aaa@cc.comApproved1227208484Remedy Application ServiceProcess Approvedgets converted to27 Jan 2007kkk.aaa@cc.comApproved1227208484Remedy Application ServiceProcess ApprovedI want that all the instances to be replaced.....using a single select statement....is it possible to replace all the occurences? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 04:09:21
|
| yup.the values are in different fields right? |
|
|
|
saurabh122
Starting Member
16 Posts |
Posted - 2009-01-20 : 05:05:29
|
| no....the entire string in which multiple occurences of dates are present...is present in a single field..... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 05:11:05
|
| then you need to use replace() |
|
|
|
saurabh122
Starting Member
16 Posts |
Posted - 2009-01-20 : 09:07:36
|
| i have used replace.....but the patindex that i use inside the replace exits after the first occurence....the query is as follows:select replace(substring(Approval_Audit_Trail,PATINDEX('12%',Approval_Audit_Trail),datalength(Approval_Audit_Trail)), substring(Approval_Audit_Trail,PATINDEX('1%',Approval_Audit_Trail), 10 ), dbo.fn_adjusted_date(substring(Approval_Audit_Trail,PATINDEX('1%',Approval_Audit_Trail), 10 )) ) from CHG_ChangeAPDetailSignature |
|
|
|
|
|
|
|
|
multiple occurences to be replaced in a string