convert date to 01/01/2004 3

[vionca]

Hi there,

I just looked up the convert function and I hoped that this function could turn 01/01/2004 00:00:00 into simply 01/01/2004.

Here's what I used: convert(datetime, myContractDate, 103)

I seem to be getting no change at all.

I admit I didn't really understand what the BOL had to say about Convert. Could someone point me in the right direction?

Thanks,
Vionca

 

[nigelrivett]

Do you have a datetime?

convert(varchar(10), myContractDate, 103)



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

To explain - the system has to convert to a character string to display. Your convert was leaving it as a datetime which is then converted by the system to the default format which includes the time. You need to convert explicitly to a character string so that the system does not have to convert for output.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[donutman]

This operation is so common that it's worth making creating a system function to do the job. faq181-86
-Karl

 

[donutman]

Guess it would help if I pointed you to the right FAQ!
faq183-5075
-Karl

 

[nigelrivett]

Except that returns a datetime with a zero time so won't help. Must admit I don't see the point of it as it's so simple.

Also the function given is incorrect as it depends on the date format setting for the system - won't work in all cases and could error.

It should be
convert(varchar(8),@date,112)

But you can use that instead of the function.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[BuGlen]

Q: Is there a performance issue observed when using system functions in the results of queries?

I think there was a discussion that using them in the results keeps SQL Server from fully optimizing the query.

Thoughts?

Hope this helps.

- Glen

Know thy data.

 

[tektipdjango]

Have you try, as convert is a reverse function :

convert(convert(myContractDate, 103),103)

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[tektipdjango]

sorry I misunderstood the question, I thought you were trying to suppress the time part

eg 01/01/2004 07:45:23 >>> 01/01/2004 00:00:00

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[vionca]

Thanks donutman and nigelrivett. I will give both a try.

Also, BuGlen, if you're still interested in UDF performance issues:

http://www.winnetmag.com/Article/ArticleID/42139/42139.html

 

[donutman]

That's a very interesting article about UDF's effect upon performance. I'm not positive, but I think the performance problem exists when the UDF does its "own" query. The very same thing can be done without a UDF, just by poorly writing the query in the first place.
So the question is, "Are all UDF bad?". I don't mean should you never use them, but do they always convert a "set" operation into a row by row operation? I don't think so, but I'd like to hear the opinion of other frequent contributors.
-Karl

 

[TJRTech]

Although less than scientific, I just ran a test with a table that has 1.67 Million records. The table has a datetime column, and I have a UDF that returns the "TwelveAM" equivalent of that datetime.

When running with the UDF as part of the select, the query executes in an average of 25 secs.

When I perform the conversion inline then it then it takes on average 22 secs.

The UDF code follows:

CREATE FUNCTION [dbo].[TwelveAM] (@dt datetime)  
RETURNS datetime  AS  
BEGIN 
  return convert(datetime, convert(varchar(11), @dt))
END

Oh, and if I do NO conversion, it takes on average 22 seconds to return from the SELECT (the same as doing the conversion inline).

The redacted queries follow:

select dbo.TwelveAM(COL_DATE_TIME) FROM MYTABLE

select convert(datetime, convert(varchar(11), COL_DATE_TIME)) FROM MYTABLE

select COL_DATE_TIME FROM MYTABLE.

So, it appears, that in this case anyway, the queries run about 14% slower when using the UDF. What this says to me is that SQL really does some awesome work optimizing the computation of columns, and you will pay a modest performance price for calling even the simplest UDF.

Regards,
TR

 

[BuGlen]

Nice analysis TR!

This is what I suspected with regard to inline vs. UDF computation. Maybe we'll see better UDF optimization with Yukon and the integration of the .Net framework.

- Glen

 

[donutman]

Nice analysis indeed!
And I agree with your conclusions. The main reason for having a simple UDF like that is to facilitate the writing of joins where you would likely use it more than once!
Of course to keep things in perspective, most tables and queries that I work with run in less than a sec, so even a 50% penalty isn't something to get worked up about. But still...
I decided to look at the performance of a UDF that returns a Select * from table of 300,000 rows. Here’s what I got:

4 sec to run Select * from MyTable
6 sec to run Select * from MyFunction

8 sec to run Select * from MyTable inner join MyTable etc
57 sec to run Select * from MyTable inner join MyFunction etc

-Karl

 

[TJRTech]

Yes, using UDFs that return tables as parts of joins is pretty darn slow. I only use them in the case of small resultsets (computed lookup tables, etc). More often then not, I use UDFs that compute single values; that's where I have found the most value.

TR

 

[BuGlen]

Probably the most efficient use of UDFs presently would be the assignment of variables where the computation would only have to be made once. For example:

Declare @StartDate smalldatetime
Declare @EndDate smalldatetime

@StartDate = dbo.StartOfLastMonth_udf
@EndDate = dbo.EndOfLastMonth_udf

Select m.MyField1, m.MyField2, m.MyField3
From MyTable1 m
Where m.MyDateColumn Between @StartDate And @EndDate

This could make common functions useful without affecting performance.

- Glen

 

[TJRTech]

Yes, Glen, but that only works when you are placing computed values in the WHERE clause. Not all "problems" have that solution. Often you generate tables through a UDF that you want to join against other tables (like Donutman said), and other times you want to transform each and every value of a specific column as in the example of datetime computing of a selected record (which was the original problem).

Also, I would hope and expect that the optimizer would only do the computation ONCE for each date given your example even if you were to forego the variables and place the UDF calls directly in the WHERE clause.

TR

 

[BuGlen]

Yes, the example is probably over simplified and as you said the use of variables will not work when computing against a table value. I was simply demonstrating the most efficient case scenerio I could think of where UDFs could be used without a performance consideration.

This is getting a bit off topic from the original post though, so I'll just let it go. :~)-

- Glen