![]() Though I think magic is bad form: select datediff ( day, CAST ( 20090401 AS CHAR ( 8 )), CAST ( 20090402 AS CHAR ( 8 ))) from_int All formatting is done at the presentation layer.Ĥ) the CONVERT function when used as it is here to take a string representation and make a datetime DOES NOT impart any format the format code of 101 represents the INPUT format of the second argument.ĥ) what do you mean non-standard date format? YYYYMMDD string representation of a date is ISO standard and will be interpreted implicitly by Microsoft magic! ![]() I think this conception is some sort of COBOL hangover - again, not that it is any of my business how Microsoft does things.ģ) datetime has NO format. datetime) is that the interal workings are none of anyones' business, I don't think datetimes are stored as strings. I'm sure it merely adds cost to the queries that use it.Ģ) while the nature of abstract data types (e.g. I'm sorry, but I don't know why this article was published.ġ) this function, while it "works", does nothing but add the overhead of wrapping native functions in a UDF. Is it like what mardukes is saying couldn't we have done a convert()? Just wondering what is the advantage of using this udf? I have been wondering on the advantage of using a udf vs. He SHOULD have just done convert(cast()) or convert(convert()) - the function gained nothing, added overhead and made maintenance more difficult. When doing maintenance, one should feel comfortable upon seeing a function call that nothing happened behind the scenes orther than "calculations" to map the arguments to a return value. It's mostly just a matter of good form, but for good form you shouldn't think of functions and procedures as interchangible. Procedures are a mechanism where one can perform all sorts of tasks AND return values. It makes them a stronger DBA.Īgain, thank you for your interest and we appreciate your time and comments.įunctions & procedures are two different things. I also find it important that DBAs understand the inner workings of the RDBMS they support. My mantra is to make it simple for the end user, even if that means more overhead for me it's an inverse relationship. By wrapping it in a UDF it makes it reusable. The solution outlined will require the end users/developers/DBAs to only deal with supplying a single parameter value for the integer datetyped value no need to remember which code parameter for the CONVERT() function corresponds to what they need. While your solution is completely appropriate (and it is) I would prefer to simply the process for the end user that may not understand the various formatting opportunities available and code numbers associated with the CONVERT() function. The article was witten to outline a situation where users expect a datetime value provided from a system-supplied source to behave in a manner that is consistent with datetime datatype values, and then to realize that is not possible without conversion/casting. Thank you for taking the time to comment. SELECT SJ., _datetime(SJH., SJH.) as įROM msdb.dbo. How about the below query - we can use MSDB inbuilt function datetime to convert the run date and run time to a more readable format. These could always be werapped in an exists statement too.dbcc dbreindex ( backupfilegroup', ) dbcc dbreindex ( backupfile', ) dbcc dbreindex ( backupmediafamily', ) dbcc dbreindex ( backupmediaset', ) dbcc dbreindex ( backupset', ) dbcc dbreindex ( restorefile', ) dbcc dbreindex ( restorefilegroup', ) dbcc dbreindex ( restorehistory', ) Thanks Brent! Probably only if you have ever performed a filegroup backup I'm not sure. You might not need the backupfilegroup table, as it doesn't exist on all my servers I'm nto sure why. I also added a step to reindex the tables it touched as well. I set a limit on how far back we want to save, created a job to run the sp_delete_backuphistory. It's a good thing we only use simple and daily full backups at this point. Records going back years is right! I had some going back to January of 2001! I had over 200k records in one of my backupset tables. ![]() Glad you did a recap of it as I now had the time. Very helpful and easy to understand, thanks!įantastic! I saw this when you posted it oinliralgy but didn't have the time to check then. I am trying to concatenate a date column and a time column (both currently strings) and then convert to datetime with 24 hour format. Very simple, especially when the format is YYYYMMDD.Ĭan you please provide an example of also including the run_time column in your calculation. ![]() I use DATEFROMPARTS when confronted with this type of thing. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |