convert function - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

convert function

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-15 : 02:20:17

Hi All,

I have to convert all the nvarchar(50) data to either datetime or decimal format, what could be the best way

I have imported all the columns from a file to nvarchar(50) it is successfull, however now I want to convert the decimal fields to decimal(pre,scale) and date fields to datetime.

correct me If I am wrong, I am using the below stm for all such field
is the placement of ltrim rtrim rt? and is the below correct

CONVERT(DECIMAL(18,2),LTRIM(RTRIM([QTY]))) -- this is actually 18,2 in raw file
CONVERT(DATETIME,LTRIM(RTRIM([DT_ISSUED]))) this is actually date CCYYMMDD format (20051259 like)

Regards,
aak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-15 : 02:23:14

yup.it looks fine. did you try running it?

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 02:39:22

You could run an IsDate() test first, to check for no invalid values:


SELECT *
FROM MyTable
WHERE IsDate(LTRIM(RTRIM([DT_ISSUED]))) = 0

but other than that you are good-to-go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-15 : 02:52:18

quote:
Originally posted by Kristen

You could run an IsDate() test first, to check for no invalid values:

SELECT *
FROM MyTable
WHERE IsDate(LTRIM(RTRIM([DT_ISSUED]))) = 0

but other than that you are good-to-go

and while using ISDATE() to check for date values make sure you use condition LEN(datefield) >=8 if you want to specifically check for formats like ccyymmdd and time part

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-15 : 03:00:21

Thanks I was using

, CONVERT(DATETIME,LTRIM(RTRIM([DT_DATE])),120)

is giving arthematic overflow error, for some dates, hmm I dont want time factor in date, I want only 2015-08-15
is it because the time factor it is giving err, coz all are dates and in the format YYYYMMDD with no time in raw data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-15 : 03:06:49

quote:
Originally posted by aakcse

Thanks I was using

, CONVERT(DATETIME,LTRIM(RTRIM([DT_DATE])),120)

is giving arthematic overflow error, for some dates, hmm I dont want time factor in date, I want only 2015-08-15
is it because the time factor it is giving err, coz all are dates and in the format YYYYMMDD with no time in raw data.

it may be because you've dates existing in other formats. Are you sure all of them are in same consistent format?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-15 : 03:12:07

I checked it and found some records has date as '00000000' hmm how can I take this data into datetime any clue.. or cannot take at all
can date be like 0000-00-00?

Thanks all

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 03:32:58

CONVERT(DATETIME, NullIf(LTRIM(RTRIM([DT_DATE])), '00000000'), 120)

This will give you NULL when the date value is "'00000000'"

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 03:33:40

Use the IsDate() test I recommended to locate any other invalid values

(its not 100% foolproof though

)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-15 : 03:44:48

quote:
Originally posted by Kristen

Use the IsDate() test I recommended to locate any other invalid values (its not 100% foolproof though )

yeah true. thats why i most times use len(field)>=8 also along with it to avoid values like 2009 etc for which also it returns 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-15 : 04:15:19

quote:
Originally posted by aakcse

Thanks I was using

, CONVERT(DATETIME,LTRIM(RTRIM([DT_DATE])),120)

is giving arthematic overflow error, for some dates, hmm I dont want time factor in date, I want only 2015-08-15
is it because the time factor it is giving err, coz all are dates and in the format YYYYMMDD with no time in raw data.

Why are you using 120 instead of 112?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 04:43:00

Here's my function for converting string dates to datetime. Returns NULL if the date is invalid.

@aakcse: you could use this to "wrap" the dates in your conversion.

(The function also supports optional Time element; date must be in format ccyymmdd, but it could support other formats, and optional inclusion of "-" such as "ccyy-mm-dd" [note "ccyy-mm-dd" is handled as-is on MOST but NOT all locales]


--
PRINT 'Create function kk_fn_UTIL_String2DateTime'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_String2DateTime]') 
    AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.kk_fn_UTIL_String2DateTime
GO

CREATE FUNCTION dbo.kk_fn_UTIL_String2DateTime
(
	-- String Date Value to convert - format is yyyymmddhhmmssmmm - later parts ignored (minimum is "yyyymmdd")
	@strDate	varchar(8000)
)
RETURNS datetime
/* WITH ENCRYPTION */
AS
/*
 * kk_fn_UTIL_String2DateTime	Convert string date to datetime datatype
 *				NULL/invalid returned as NULL
 * 	SELECT	dbo.kk_fn_UTIL_String2DateTime('yymmddhhmmssmmm')
 *
 * Returns:
 *
 * 	datetime
 *
 * HISTORY:
 *
 * 21-Jan-2006 KBM  Started
 */
BEGIN
DECLARE	@intLen		smallint,
	@strTemp	varchar(21)

	SELECT	@strTemp = RTrim(LTrim(@strDate))
	SELECT	@intLen = LEN(@strTemp)
	IF @intLen < 8	-- Minimum is yyyymmdd
	BEGIN
		SELECT	@strTemp = NULL
	END
	ELSE
	BEGIN		
		SELECT	@strTemp = 
			LEFT(@strTemp, 8) 
			+ CASE WHEN @intLen < 9 THEN '' 
			ELSE 
				  ' ' + substring(@strTemp, 9, 2) 
				+ ':' + CASE WHEN @intLen < 11 THEN '00' ELSE substring(@strTemp, 11, 2) END
				+ ':' + CASE WHEN @intLen < 13 THEN '00' ELSE substring(@strTemp, 13, 2) END
				+ CASE WHEN @intLen < 15 THEN '' ELSE '.' + substring(@strTemp, 15, 3) END
			END
	END

	IF ISDATE(@strTemp) = 0
	BEGIN
		SELECT	@strTemp = NULL
	END
	RETURN CONVERT(datetime, @strTemp)

/** TEST RIG

SELECT	'20010203040506123',	dbo.kk_fn_UTIL_String2DateTime('20010203040506123')
SELECT	'2001020304050612',	dbo.kk_fn_UTIL_String2DateTime('2001020304050612')
SELECT	'200102030405061',	dbo.kk_fn_UTIL_String2DateTime('200102030405061')
SELECT	'20010203040506',	dbo.kk_fn_UTIL_String2DateTime('20010203040506')
SELECT	'2001020304051',	dbo.kk_fn_UTIL_String2DateTime('2001020304051')
SELECT	'200102030405',		dbo.kk_fn_UTIL_String2DateTime('200102030405')
SELECT	'20010203041',		dbo.kk_fn_UTIL_String2DateTime('20010203041')
SELECT	'2001020304',		dbo.kk_fn_UTIL_String2DateTime('2001020304')
SELECT	'200102031',		dbo.kk_fn_UTIL_String2DateTime('200102031')
SELECT	'20010203',		dbo.kk_fn_UTIL_String2DateTime('20010203')
SELECT	'20010203 ',		dbo.kk_fn_UTIL_String2DateTime('20010203 ')
SELECT	' 20010203',		dbo.kk_fn_UTIL_String2DateTime(' 20010203')
SELECT	' 20010203 ',		dbo.kk_fn_UTIL_String2DateTime(' 20010203 ')
SELECT	'2001021',		dbo.kk_fn_UTIL_String2DateTime('2001021')


**/
--==================== kk_fn_UTIL_String2DateTime ====================--
END
GO
PRINT 'Create function kk_fn_UTIL_String2DateTime DONE'
GO
--

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-15 : 04:52:35

This reminds me mine

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources