December 29, 2011 at 8:59 am
I'm having a problem with a simple Scalar UDF.
CREATE FUNCTION udf_ConvertDB2toSQL
RETURNS DateTime
AS
BEGIN
DECLARE @CSC_DATE varchar(7)
DECLARE @SQLDate Datetime
SET @CSC_DATE = '1111130'
SET @SQLDate = (SELECT CAST(SUBSTRING(@CSC_DATE, 4 ,2)+ '/' +
SUBSTRING(@CSC_DATE, 6 ,2) + '/' +
(CASE LEFT(@CSC_DATE, 1)
WHEN 0 THEN '19'
WHEN 1 THEN '20'
END) +
SUBSTRING(@CSC_DATE, 2 ,2) AS datetime) AS CSC_DateTime)
SELECT @SQLDate
END
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2011 at 9:21 am
CREATEFUNCTION[dbo].[fncConvertAS400Date]
(@AS400Datevarchar(9))
RETURNSdatetime
-------------------------------------------------------------------------------------------
AS
-------------------------------------------------------------------------------------------
BEGIN--Beginning of Function
-------------------------------------------------------------------------------------------
DECLARE@numDateint
DECLARE@varDatevarchar(10)
DECLARE@rtnDatedatetime
-----------------------------------------------------------------------------------
IfIsNumeric(@AS400Date) = 1
BEGIN
SET@numDate =CAST(@AS400DateASint)
SET@numDate =@numDate + 19000000
SET@varDate =CAST(@numDateASvarchar(8))
SET@varDate =Substring(@varDate, 5, 2) + '/' +--Month
Right(@varDate, 2) + '/' +--Day
LEFT(@varDate, 4)--Year
END
ELSE
BEGIN
SET@varDate =NULL
END
-----------------------------------------------------------------------------------
IfIsDate(@varDate) = 1
SET@rtnDate =CAST(@varDateASdatetime)
ELSE
SET@rtnDate =NULL
-----------------------------------------------------------------------------------
RETURN@rtnDate
-------------------------------------------------------------------------------------------
END--End of Function
-------------------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2011 at 9:21 am
shouldn't it look something like this?
CREATE FUNCTION udf_ConvertDB2toSQL(@CSC_DATE varchar(7))
RETURNS DateTime
AS
BEGIN
DECLARE @SQLDate Datetime
SET @SQLDate = (SELECT CAST(SUBSTRING(@CSC_DATE, 4 ,2)+ '/' +
SUBSTRING(@CSC_DATE, 6 ,2) + '/' +
(CASE LEFT(@CSC_DATE, 1)
WHEN 0 THEN '19'
WHEN 1 THEN '20'
END) +
SUBSTRING(@CSC_DATE, 2 ,2) AS datetime) AS CSC_DateTime)
RETURN @SQLDate
END
GO
SELECT '1111130',dbo.udf_ConvertDB2toSQL ('1111130')
Lowell
December 29, 2011 at 9:52 am
I prefer using STUFF rather than SUBSTRING and concatenation for a couple of reasons:
* It's language independent. The substring approach will fail if the language settings don't match the reformatted string, (i.e., most of the world). Of course you can overcome this by using CONVERT instead of CAST and specifying the format or, even better, reformatting to a language independent format.
* It doesn't require an explicit CAST/CONVERT. The substring (as written) requires a CONVERT to make it independent of the language settings.
* It's shorter. Admittedly not by much.
SET @SQLDate = Stuff(
Stuff(
Stuff(@CSC_Date, 6, 0, '-')
, 4, 0, '-')
, 1, 1, CASE LEFT(@CSC_DATE, 1) WHEN 0 THEN '19' WHEN 1 THEN '20' END
)
I find it easier to work from right-to-left when using STUFF(), so that you don't have to worry about the position changing based on the changes that you've made earlier in the calculation.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 29, 2011 at 10:03 am
I should mention another advantage of STUFF(). You're not cutting the string into pieces and reassembling them, so you don't have to worry about getting the pieces in the wrong order and it will also be obvious if you have a fencepost error.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 29, 2011 at 10:09 am
Very clever. I appreciate all of the feedback from everyone.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 30, 2011 at 7:34 am
How can I modify the function so that you can pass a data type and it returns the result in the specified format and avoid using the Convert Function
as listed below?
DECLARE @AS400_DATE varchar(7)
SET @AS400_DATE = '0991130'
SELECT dbo.fncConvertAS400Date (@AS400_DATE)
SELECT CONVERT(varchar (10),dbo.fncConvertAS400Date (@AS400_DATE), 121) AS [Date]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 30, 2011 at 7:44 am
i think this minor modification will do what you are asking; instead of returning a datatime, i changed it to returnt eh varchar(10);
inside it it's using convert twice, once to get it to datetime, and the other back again to get it in the 121 format.
CREATE FUNCTION [dbo].[FNCCONVERTAS400DATE] (@AS400Date VARCHAR(9))
RETURNS varchar(10)
AS
BEGIN -- Beginning of Function
DECLARE @numDate INT
DECLARE @varDate VARCHAR(10)
DECLARE @rtnDate DATETIME
IF ISNUMERIC(@AS400Date) = 1
BEGIN
SET @numDate = CAST(@AS400Date AS INT)
SET @numDate = @numDate + 19000000
SET @varDate = CAST(@numDate AS VARCHAR(8))
SET @varDate = SUBSTRING(@varDate, 5, 2) + '/' + -- Month
RIGHT(@varDate, 2) + '/' + -- Day
LEFT(@varDate, 4) -- Year
END
ELSE
BEGIN
SET @varDate = NULL
END
IF ISDATE(@varDate) = 1
SET @rtnDate = CONVERT(varchar (10),CONVERT(datetime,@varDate), 121)
ELSE
SET @rtnDate = NULL
RETURN @rtnDate
END -- End of Function
Lowell
December 30, 2011 at 11:23 am
CELKO (12/30/2011)
A function or a procedure is names with <verb>_<object> and does not have the meta-data like “udf_”; name things for what they are, not how they are implemented.
Naming conventions are simply conventions. There is no right or wrong. As long as they are consistent and meaningful, it doesn't really matter exactly what convention you follow.
Then rest of your code is COBOL written in SQL! ARRRGH! Record at a time with a function, character by character!
Microsoft has a DATE data type now, so you can use CAST (<str> TO DATE) n, which is just like the DB2. They both use the ANSI/ISO and ISO-8601 date formats. The format is “yyyy-mm-dd” the only one allowed in Stanadar5d SQL.
The first rule of code is that it has to WORK. Since the incoming data is not in a format recognized by CAST(), you're "solution" doesn't meet the first rule. The whole point of the string manipulation is to convert a non-standard format into a standard format. It's pointless to program as if your data were in a standard format, when you know that it's not and that it's not likely to change to a standard format.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 30, 2011 at 12:59 pm
Happy New Years.:w00t:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 30, 2011 at 1:53 pm
Whatever you do, make it an Inline Table Valued function !!!!!!!
( and use it with cross apply )
That is, if you would like it to perform.
Test it, test IT !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 30, 2011 at 7:17 pm
ALZDBA (12/30/2011)
Whatever you do, make it an Inline Table Valued function
Absolutely. If the logic is not suitable for an in-line TVF, make it a CLR scalar function instead - these perform very much better than T-SQL scalar functions, and do not prevent parallelism (assuming no data access).
December 30, 2011 at 10:05 pm
drew.allen (12/30/2011)
CELKO (12/30/2011)
A function or a procedure is names with <verb>_<object> and does not have the meta-data like “udf_”; name things for what they are, not how they are implemented.Naming conventions are simply conventions. There is no right or wrong. As long as they are consistent and meaningful, it doesn't really matter exactly what convention you follow.
Except maybe for the underscore (which I usually try to avoid in favor of proper casing), I have to side with Joe on this one, Drew. And, yeah, it may be a personal choice but my choice would be not to use anything similar to Hungarian Notation because of the problems it can cause.
For example, if you have a table named tbl_something and later decide that it would be better to replace the table with a view, you either have to change all the code from tbl_something to vw_something or put up with a view that has a "tbl_" prefix. :pinch:
A similar example would occur with variables. If you use @iSerialNumber to indicate that the serial number is an INTEGER and you later need to change the datatype of the SerialNumber column to, say, VARCHAR(20), will you go back through all of the code and change @iSerialNumber to @strSerialNumber or would it be easier to just change the declared datatype in the code?
I'll also admit that when I first saw the function name of udf_ConvertDB2toSQL, I had no idea what the function was going to do. If it were simply called ConvertDB2DateToDateTime,
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2011 at 10:53 pm
drew.allen (12/30/2011)
Naming conventions are simply conventions. There is no right or wrong. As long as they are consistent and meaningful, it doesn't really matter exactly what convention you follow.
Exactly.
December 30, 2011 at 11:01 pm
Welsh Corgi (12/29/2011)
I'm having a problem with a simple Scalar UDF.
As ALZDBA suggested and Paul backed up, you should use an iTVF (Inline Table Valued Function) for this (unless you just want to use a simple formula to do it for you) and then use it in a CROSS APPLY. Here's how you can do all of that...
First, the function can actually be simplified quite a bit as follows (including a "forced error if the first character isn't a '0' or a '1'):
CREATE FUNCTION dbo.ConvertDB2DateToSqlDateTime
(
@CscDate CHAR(7)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN (
SELECT SqlDateTime =
CAST(
STUFF(
@CscDate,
1,
1,
CASE LEFT(@CscDate,1)
WHEN '0' THEN '19'
WHEN '1' THEN '20'
ELSE 'BadYear'
END
)
AS DATETIME)
)
;
Notice also that I've used '0' and '1' instead of just 0 and 1 to avoid some implicit conversions.
Now, let's say you have a table like the following (just an example):
--===== Create and populate a test table with multiple Csc Dates.
-- This isn't a part of the solution. We're just building test data here.
WITH
cteIsoDates AS
( --=== Build "ISO" dates from '1900-01-01' up to and not including '2100-01-01'
SELECT TOP 1000
IsoDate = CONVERT(CHAR(8),DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'1900','2100'),'1900'),112)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
) --=== Put both dates in the new table formed on-the-fly (IsoDate included just for grins)
SELECT IsoDate,
CscDate = STUFF(IsoDate,1,2,CASE LEFT(IsoDate,2) WHEN '19' THEN '0' ELSE '1' END)
INTO #MyHead
FROM cteIsoDates
;
Then, using CROSS APPLY, this is how you would use the function...
--===== Solve the problem with the iTVF we built earlier
SELECT mh.IsoDate,
mh.CscDate,
cd.SqlDateTime
FROM #MyHead mh
CROSS APPLY dbo.ConvertDB2DateToSqlDateTime(CscDate) cd
;
Of course, you could actually skip the function...
SELECT IsoDate,
CscDate,
SqlDateTime = CAST(STUFF(CscDate,1,1,CASE LEFT(CscDate,1) WHEN '0' THEN '19' WHEN '1' THEN '20' ELSE 'BadYear' END) AS DATETIME)
FROM #MyHead
;
Please let me know if you have any additional questions. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply