Convert date to MMDDYYYY 2

[micang]

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(varchar(10), getdate(), 101), '/', ')

My question is, is this the best solution for this task?

Many thanks
Michael

 

[gmmastros]

That's probably the best way.

There are other ways of doing this, but it would probably involve more calculations and/or conversions. This solution is really simple, so it's easier to understand if you ever need to look at this code again in the future.

Since other methods are not likely to be any faster, and this is simple, it is probably the best.

You're missing a single quote from the code, but I assume that's just a typo.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ElEye]

Another option, depending on your SQL Server version: SELECT (FORMAT(GETDATE(),'MMddyyy'))

Dave
[

http://flashspot.com

]

 

[Olaf Doschke]

>the source date is a datetime (2015-09-01 00:00:00.000) format

You're making a mistake I see most people do. You believe a datetime is stored in the format you see, but a datetime field type is stored binary in 8 or more bytes, but typically less than characters are used for display The binary format can be something like seconds from 1st January 1970 midnight, which is a unix timestamp format. In the same manner integer numbers are stored in 4 bytes (32 bits), while the display of them can need up to 11 characters in the negative billion range. You don't ever get to see that, but if your field is of datetime type or smalldatetime, there is no format attached to it in the storage of the datetime values.

The display of data always is human readable and how dates display in SSMS depends on your setting of SET DATEFORMAT and some mere locale settings (eg the names of months in long date formats). If you query SELECT datetimefield FROM table into some frontends, their display can differ, but ODBC and OLEDB drivers have standard ways of representing datetimes and they are not the strings you see. So a browse of data is never WYSWiWYG, the underlying binary storage is not locale specific but more to the point of the pureness and universality of the values and also optimized for computations with the data.


The consequence of that is, your query code may not need to make the conversion to the output format, some things like export to CSV are automagically outputting datetimes in the output format typical formatting and encoding, depending on toolings you use. Eg you query into a datetime type into an access frontend 1:1 as OLE datetime type of a recordset and define the format in form controls there or you convert datetimes to dates only and you can use frontend language code to make MDY, DMY or YMD formats, different separators like / or . or the display is locale specific via the OS.

It pays to keep a datetime type a datetime type as long as you can and to make the conversion as last step to always be able to do some calculations also in reporting engines or frontend language, even in javascript. That way it's rarely the topic of SQL Server to do these string conversions and actually the convert options SQL Server offers are quite broad for a database. As an example, the bcp tool (bulk copy) expects YYYY-MM-DD format in flat files as input and AFAIK it outputs date columns in that format, too, without you preparing them and no matter what DATEFORMAT setting you have in the server instance or on the database level, because that is a flat file standard and bcp knows, what it expects and so also delivers it. You certainly have the option to define formats in format files, when using bcp.

Bye, Olaf.

 

[micang]

@gmmastros - thanks for your input, appreciate it.
@ElEye - thank you for that - I am using 2008. I was not aware of the FORMAT function - we are upgrading to 2012 sometime this year, so this is good to know.
@OlafDoschke - thank you for your detailed explanation. With regards to how I am exporting the data, I am using SSIS. I will look at SSIS and see if I can transform the output there.

Thank you all.
Michael