nvarchar to Datetime

[mike55]

What is the best way,

 

Have tried both Convert and Cast but nether seem to work.

 

Mike55

[Nerseus]

I use CONVERT, works great!

 

If that didn't help, maybe you could post a bit of code and values you're using? Any other info that seems relevant.

Help me... Help you!

- Jerry McGuire

 

 

-ner

[mike55]

Can you give me the exact Convert statement that your are using?

 

Have tried the following conversions but with no luck:

1. Cast (@data5 as datetime)

2. Convert(datetime, @data5, 101)

3. Convert(datetime, @data5)

 

 

Here is the Stored Procedure I am using, @data5 and @data19 are the datetime parameters:

CREATE PROCEDURE dbo.spTEMPtable1
(
@columnData5 nvarchar(500),
@columnData19 nvarchar(500)
)
AS

BEGIN

SET NOCOUNT ON

DECLARE @data5 datetime
DECLARE @data19 datetime

DECLARE @PosColumn5 int
DECLARE @PosColumn19 int


SET @columnData5 = LTRIM(RTRIM(@columnData5))+','
SET @columnData19 = LTRIM(RTRIM(@columnData19))+','

SET @PosColumn5 = CHARINDEX(',', @columnData5, 1)
SET @PosColumn19 = CHARINDEX(',', @columnData19, 1) 


IF REPLACE(@columnData5, ',','') <> '' 
BEGIN
	WHILE @PosColumn5 > 0
	BEGIN
		SET @data5 = LTRIM(RTRIM(LEFT(@columnData5, @PosColumn5 - 1)))
		SET @data19 = LTRIM(RTRIM(LEFT(@columnData19, @PosColumn19 - 1)))
		
		IF @data5 <> ''
		BEGIN
		--SET @data5 ='12/12/2005'
		SET @data19 ='01/01/2005'

--			Select @data5 = convert(datetime, @tempData5)

--			SET @data5 = CAST(@tempData5 as datetime(8))
			INSERT INTO TEMPtable1
			VALUES (Cast(@data5 as datetime),   @data19) 
		END

		SET @columnData5 = RIGHT(@columnData5, LEN(@columnData5) - @PosColumn5)
		SET @columnData19 = RIGHT(@columnData19, LEN(@columnData19) - @PosColumn19)
		SET @PosColumn5 = CHARINDEX(',', @columnData5, 1)
		SET @PosColumn19 = CHARINDEX(',', @columnData19, 1)
	END
END
END
GO

 

And here are the parameters that I am passing in of dates:

SET @columnData5 = '27/05/1982, 27/05/1985'
SET @columnData19 = '27/05/1982, 27/05/1985'

Edited September 6, 2005 by mike55

[Nerseus]

At the very least, your dates are in the wrong format - you should try MM/DD/YYYY instead of DD/MM/YYYY. It seems odd, but that's what SQL Server wants (that's the USA standard).

 

As a simple test, try this in Query Analyzer:

SELECT CONVERT(datetime, '27/05/1982')

 

That should fail (I copied the date from your sample).

The following should work:

SELECT CONVERT(datetime, '05/27/1982')

 

The rest of that code is so bad-looking that I didn't want to see if it work. I assume you've tested it and the parsing all works well and you're just having issues with the date formats.

 

If you want other suggestions on what to clean up, we could all help. For starters, I'd move all of the string parsing out of the proc if at ALL possible. The DB engine supports string functions like how you're using them, and even WHILE statements. But it doesn't mean it's good at it. In fact, it's very, very bad. If you have to pass in some unknown number of dates as a string, I'd consider using dynamic SQL (still done through a proc) over what you've got.

 

-ner

[mike55]

I know, the code doesn't look very appealing. The aim is to take in lines of comma seperated values, extract the data from the CSV lines and insert the data into the database, basically a looping function.

 

Mike55