Replacing cursor issue
-
hi, I have a sp that will run about everyday that is to convert nvarchar date to a datetime format, but i needed to do a function to change its format first cause wasn a known tsql date format. I made a stored procedure that use a cursor to do this job, but it takes like 40 min for 500 000 rows and I was wondering if theres a way to replace my cursor to improves the performance of this stored procedure.
My stored procedure looks like this:
*********************************************************
CREATE PROCEDURE [dbo].TransformerDateTest AS
BEGIN
SET NOCOUNT ON
DECLARE VisitesDates CURSOR STATIC LOCAL FOR
SELECT
[VisID],
[VisDate]
FROM
[dbo].[IISVisite]
WHERE
VisDateHeure IS NULL
OPEN VisitesDates
DECLARE @visiteID AS INT
DECLARE @dateSource AS NVARCHAR(50)
FETCH NEXT FROM
VisitesDates
INTO
@visiteID,
@dateSource
WHILE(@@FETCH_STATUS = 0) BEGIN
UPDATE IISVisite
SET VisDateHeure = CONVERT (datetime, [dbo].[FormatDate](@dateSource), 20)
WHERE VisID = @visiteID
FETCH NEXT FROM
VisitesDates
INTO
@visiteID,
@dateSource
END
CLOSE VisitesDates
DEALLOCATE VisitesDates
*********************************************************
Thx for your reply and sry for the quality of my English
-
UPDATE dbo.IISVisite
SET VisDateHeure = CONVERT (datetime, [dbo].[FormatDate](@dateSource), 20)
WHERE VisDateHeure IS NULL
- UPDATE dbo.IISVisite
SET VisDateHeure = CONVERT (datetime, [dbo].[FormatDate](@dateSource), 20)
WHERE VisDateHeure IS NULL
Would work if i wasn using my cursor for @dateSource? -
Sorry :
UPDATE dbo.IISVisite
SET VisDateHeure = CONVERT(datetime, [dbo].[FormatDate](VisDate), 20)
WHERE VisDateHeure IS NULL
btw I don't think you need to convert the values with that function unless you expect bad data... that coudl speed things up a little.
-
thx, i tried it and take likes 30 second, With the cursor it was 40min
hmm i know would maybe speed up to if i could use the format from the nvarchar date column, but this is the format it has:
03/Jun/2005:11:16:40
this is not any of sql date format
thx againt for your help
-
Alright. HTH.
-
If all you need is to parse the strange date format, then Try:
DECLARE @DateStr varchar(50)
SET @DateStr = '03/Jun/2005:11:16:40'
SELECT CONVERT(varchar,REPLACE(LEFT(@DateStr,11),'/',' ')+' '+ RIGHT(@DateStr,8),113) AS Europe_default
Andy
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply