nvarchar to Datetime

I use CONVERT, works great!

If that didnt help, maybe you could post a bit of code and values youre using? Any other info that seems relevant.
Help me... Help you!
Click to expand...
- Jerry McGuire


-ner
 
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:
Code: 
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:
Code: 
SET @columnData5 = 27/05/1982, 27/05/1985
SET @columnData19 = 27/05/1982, 27/05/1985
 
Last edited by a moderator:
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 thats what SQL Server wants (thats 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 didnt want to see if it work. I assume youve tested it and the parsing all works well and youre just having issues with the date formats.

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

-ner
 
I know, the code doesnt 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
 
Back
Top