February 9, 2016 at 3:05 pm
Hi All,
I need to export data out to a txt file with the date format of MMDDYYYY - the source date is a datetime (2015-09-01 00:00:00.000) format - below is the solution I have:
select replace(convert(char(10), getdate(), 101), '/', '')
My question is, is this the best solution for this task?
Many thanks
Michael
February 9, 2016 at 3:29 pm
micang (2/9/2016)
Hi All,I need to export data out to a txt file with the date format of MMDDYYYY - the source date is a datetime (2015-09-01 00:00:00.000) format - below is the solution I have:
select replace(convert(char(10), getdate(), 101), '/', '')My question is, is this the best solution for this task?
Many thanks
Michael
Not sure what you are looking for as an answer but your posted solution will work. That is a somewhat strange format but whatever, you need it the way you need it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2016 at 3:30 pm
Yeah, should be good, except that I'd use varchar instead of char so that you don't have trailing spaces after the /s are replaced.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 9, 2016 at 3:36 pm
Sean Lange (2/9/2016)
micang (2/9/2016)
Hi All,I need to export data out to a txt file with the date format of MMDDYYYY - the source date is a datetime (2015-09-01 00:00:00.000) format - below is the solution I have:
select replace(convert(char(10), getdate(), 101), '/', '')My question is, is this the best solution for this task?
Many thanks
Michael
Not sure what you are looking for as an answer but your posted solution will work. That is a somewhat strange format but whatever, you need it the way you need it.
Thank for your input, I just kind of found it strange that there is no set convert\cast function and avoid the replace to do this and was wondering if I missed it. Yeah, it is a weird format, but the client has asked for it like this - my initial reaction to the spec was the same as yours - "strange".
Thanks
Michael
February 9, 2016 at 3:37 pm
ScottPletcher (2/9/2016)
Yeah, should be good, except that I'd use varchar instead of char so that you don't have trailing spaces after the /s are replaced.
Thanks, I will change to varchar, that does make sense, appreciate your input.
Thanks
Michael
February 9, 2016 at 9:44 pm
The performance of FORMAT is very poor when used over large sets of data which is unfortunate because the syntax is more readable than any of the other methods available for work like this. This happens to be one place where I would use FORMAT **IF** your example is representative of how you need to use it, i.e. you want to get the format of the current date into a variable and use it over and over, like this:
DECLARE @MMDDYYYY CHAR(8) = FORMAT(GETDATE(), 'MMddyyyy');
SELECT @MMDDYYYY AS [@MMDDYYYY]
If your intent is to format datetime data stored in a column then do not use FORMAT, use REPLACE with CONVERT as shown in your original post.
nevermind...just noticed you're on SQL 2008
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 10, 2016 at 8:19 am
Orlando Colamatteo (2/9/2016)
The performance of FORMAT is very poor when used over large sets of data which is unfortunate because the syntax is more readable than any of the other methods available for work like this. This happens to be one place where I would use FORMAT **IF** your example is representative of how you need to use it, i.e. you want to get the format of the current date into a variable and use it over and over, like this:
DECLARE @MMDDYYYY CHAR(8) = FORMAT(GETDATE(), 'MMddyyyy');SELECT @MMDDYYYY AS [@MMDDYYYY]
If your intent is to format datetime data stored in a column then do not use FORMAT, use REPLACE with CONVERT as shown in your original post.
nevermind...just noticed you're on SQL 2008
Thank you very much for this information, it has not gone wasted as sometime this year we will be upgrading to 2012, so this is very helpful, appreciate it.
Thanks
Michael
February 10, 2016 at 11:38 am
micang (2/10/2016)
Orlando Colamatteo (2/9/2016)
The performance of FORMAT is very poor when used over large sets of data which is unfortunate because the syntax is more readable than any of the other methods available for work like this. This happens to be one place where I would use FORMAT **IF** your example is representative of how you need to use it, i.e. you want to get the format of the current date into a variable and use it over and over, like this:
DECLARE @MMDDYYYY CHAR(8) = FORMAT(GETDATE(), 'MMddyyyy');SELECT @MMDDYYYY AS [@MMDDYYYY]
If your intent is to format datetime data stored in a column then do not use FORMAT, use REPLACE with CONVERT as shown in your original post.
nevermind...just noticed you're on SQL 2008
Thank you very much for this information, it has not gone wasted as sometime this year we will be upgrading to 2012, so this is very helpful, appreciate it.
Thanks
Michael
Be careful with this, as the FORMAT function is significantly slow. People have made performance tests showing that it can be up to 40 times slower than the CONVERT function.
February 10, 2016 at 11:44 am
Luis Cazares (2/10/2016)
micang (2/10/2016)
Orlando Colamatteo (2/9/2016)
The performance of FORMAT is very poor when used over large sets of data which is unfortunate because the syntax is more readable than any of the other methods available for work like this. This happens to be one place where I would use FORMAT **IF** your example is representative of how you need to use it, i.e. you want to get the format of the current date into a variable and use it over and over, like this:
DECLARE @MMDDYYYY CHAR(8) = FORMAT(GETDATE(), 'MMddyyyy');SELECT @MMDDYYYY AS [@MMDDYYYY]
If your intent is to format datetime data stored in a column then do not use FORMAT, use REPLACE with CONVERT as shown in your original post.
nevermind...just noticed you're on SQL 2008
Thank you very much for this information, it has not gone wasted as sometime this year we will be upgrading to 2012, so this is very helpful, appreciate it.
Thanks
Michael
Be careful with this, as the FORMAT function is significantly slow. People have made performance tests showing that it can be up to 40 times slower than the CONVERT function.
No doubt. I tried to level a similar caveat as well in my post. Only use FORMAT as you would a Scalar UDF, i.e. to assign a value to a variable or for a guaranteed single row.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 10, 2016 at 2:25 pm
Thank you for the heads up about the FORMAT function and performance of it, appreciate it.
Michael
February 10, 2016 at 3:36 pm
Orlando Colamatteo (2/10/2016)
Luis Cazares (2/10/2016)
micang (2/10/2016)
Orlando Colamatteo (2/9/2016)
The performance of FORMAT is very poor when used over large sets of data which is unfortunate because the syntax is more readable than any of the other methods available for work like this. This happens to be one place where I would use FORMAT **IF** your example is representative of how you need to use it, i.e. you want to get the format of the current date into a variable and use it over and over, like this:
DECLARE @MMDDYYYY CHAR(8) = FORMAT(GETDATE(), 'MMddyyyy');SELECT @MMDDYYYY AS [@MMDDYYYY]
If your intent is to format datetime data stored in a column then do not use FORMAT, use REPLACE with CONVERT as shown in your original post.
nevermind...just noticed you're on SQL 2008
Thank you very much for this information, it has not gone wasted as sometime this year we will be upgrading to 2012, so this is very helpful, appreciate it.
Thanks
Michael
Be careful with this, as the FORMAT function is significantly slow. People have made performance tests showing that it can be up to 40 times slower than the CONVERT function.
No doubt. I tried to level a similar caveat as well in my post. Only use FORMAT as you would a Scalar UDF, i.e. to assign a value to a variable or for a guaranteed single row.
Every one of those points is right on, from it working, being slow and being for SQL 2012 and above. One of these days, I'll get the time to finish up my own ITVF that does it well. I started it quite a while ago and want to encompass all the datetime parts, but other things always seem to get in the way. I have it working as a scalar, but need to finish converting it. I'll write it up if I ever get it done. My goal is to blow the doors off FORMAT and it won't be done until I do. 😛
February 10, 2016 at 7:27 pm
Ed Wagner (2/10/2016)
Orlando Colamatteo (2/10/2016)
Luis Cazares (2/10/2016)
micang (2/10/2016)
Orlando Colamatteo (2/9/2016)
The performance of FORMAT is very poor when used over large sets of data which is unfortunate because the syntax is more readable than any of the other methods available for work like this. This happens to be one place where I would use FORMAT **IF** your example is representative of how you need to use it, i.e. you want to get the format of the current date into a variable and use it over and over, like this:
DECLARE @MMDDYYYY CHAR(8) = FORMAT(GETDATE(), 'MMddyyyy');SELECT @MMDDYYYY AS [@MMDDYYYY]
If your intent is to format datetime data stored in a column then do not use FORMAT, use REPLACE with CONVERT as shown in your original post.
nevermind...just noticed you're on SQL 2008
Thank you very much for this information, it has not gone wasted as sometime this year we will be upgrading to 2012, so this is very helpful, appreciate it.
Thanks
Michael
Be careful with this, as the FORMAT function is significantly slow. People have made performance tests showing that it can be up to 40 times slower than the CONVERT function.
No doubt. I tried to level a similar caveat as well in my post. Only use FORMAT as you would a Scalar UDF, i.e. to assign a value to a variable or for a guaranteed single row.
Every one of those points is right on, from it working, being slow and being for SQL 2012 and above. One of these days, I'll get the time to finish up my own ITVF that does it well. I started it quite a while ago and want to encompass all the datetime parts, but other things always seem to get in the way. I have it working as a scalar, but need to finish converting it. I'll write it up if I ever get it done. My goal is to blow the doors off FORMAT and it won't be done until I do. 😛
A performant version of FORMAT would be a great add to any toolkit. I am looking forward to your version. I am going to mock up a SQLCLR table-valued function as well just to see if it has potential to exceed (hopefully by orders of magnitude) the performance of the native FORMAT.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 16, 2016 at 5:13 pm
Orlando Colamatteo (2/10/2016)
Ed Wagner (2/10/2016)
Orlando Colamatteo (2/10/2016)
Luis Cazares (2/10/2016)
micang (2/10/2016)
Orlando Colamatteo (2/9/2016)
The performance of FORMAT is very poor when used over large sets of data which is unfortunate because the syntax is more readable than any of the other methods available for work like this. This happens to be one place where I would use FORMAT **IF** your example is representative of how you need to use it, i.e. you want to get the format of the current date into a variable and use it over and over, like this:
DECLARE @MMDDYYYY CHAR(8) = FORMAT(GETDATE(), 'MMddyyyy');SELECT @MMDDYYYY AS [@MMDDYYYY]
If your intent is to format datetime data stored in a column then do not use FORMAT, use REPLACE with CONVERT as shown in your original post.
nevermind...just noticed you're on SQL 2008
Thank you very much for this information, it has not gone wasted as sometime this year we will be upgrading to 2012, so this is very helpful, appreciate it.
Thanks
Michael
Be careful with this, as the FORMAT function is significantly slow. People have made performance tests showing that it can be up to 40 times slower than the CONVERT function.
No doubt. I tried to level a similar caveat as well in my post. Only use FORMAT as you would a Scalar UDF, i.e. to assign a value to a variable or for a guaranteed single row.
Every one of those points is right on, from it working, being slow and being for SQL 2012 and above. One of these days, I'll get the time to finish up my own ITVF that does it well. I started it quite a while ago and want to encompass all the datetime parts, but other things always seem to get in the way. I have it working as a scalar, but need to finish converting it. I'll write it up if I ever get it done. My goal is to blow the doors off FORMAT and it won't be done until I do. 😛
A performant version of FORMAT would be a great add to any toolkit. I am looking forward to your version. I am going to mock up a SQLCLR table-valued function as well just to see if it has potential to exceed (hopefully by orders of magnitude) the performance of the native FORMAT.
Since this post I learned that the internals of the built-in FORMAT are in fact rooted in CLR which all but dashed my hopes of a homegrown SQLCLR object improving the situation. Nonetheless, I still wanted to try it and finally got a few minutes to do so. As expected a SQLCLR Scalar-function performs poorly. 1MM rows to a bit bucket variable as yyyyMMdd-format took 11 seconds while the equivalent using CONVERT and the same formatting style took under a second.
Now, interestingly enough, the same test using the built-in FORMAT function yielded similar performance to CONVERT. I am not sure if optimizations have been added but I am going to research this one a bit more.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 16, 2016 at 9:11 pm
Orlando Colamatteo (4/16/2016)
...Now, interestingly enough, the same test using the built-in FORMAT function yielded similar performance to CONVERT. I am not sure if optimizations have been added but I am going to research this one a bit more.
Must have been anecdotal.
After running the script from Aaron's blog post...
http://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but
...against my local SQL 2012 and SQL 2016 instances it is clear things haven't changed. I think the comparable performance I was seeing was anecdotal due to too few variations in the dates I was processing. FORMAT is great for functionality but is still quite expensive when compared to CONVERT for DATE-to-STRING formatting.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply