One of the reader Nanda of SQLAuthority.com has posted very detailed script of converting any date time in desired format. I suggest every reader of this blog to save this script in your permanent code bookmark and use it when you need it. Let us learn about User Defined Functions.
Refer the function and get familiar yourself with a different format this function support. I have added a few examples of how this function can be used at the end of the article. You can download the whole code in ZIP format as well.
Download Get Date Time in Any Format Script (ZIP)
I am not including the entire function over here as it can be very long it is very difficult to format. I strongly encourage that you download the function from the URL which I have listed above. I personally find it very helpful and interesting.
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mm/dd/yy') 'Format mm/dd/yy' GO SELECT [dbo].[ufsFormat] ('8/7/2008', 'hh:mm:ss') 'Format hh:mm:ss' GO SELECT [dbo].[ufsFormat] ('8/7/2008', 'mmm') 'Format mmm' GO SELECT [dbo].[ufsFormat] ('8/7/2008', 'Mmm dd yyyy hh:mm:ss:ms AM/PM') 'Format Mmm dd yyyy hh:mm:ss:ms AM/PM' GO SELECT [dbo].[ufsFormat] ('8/7/2008', '#') 'Format #' GO
Let me know what you think of this function. I strongly encourage that you try this out. However, in the latest version of SQL Server, there is a better function called FORMAT which can the date and time in a better format.
Let me know what you think of this new blog article SQL SERVER – Learning New Multipurpose FORMAT Function.
Reference: Pinal Dave (https://blog.sqlauthority.com/), Nanda
Hi, I am working in manufacturing company and they need date code for example if it is 2017/10/11 then the code should appear 7XB(7 is year 17 and X is month oct and B is day 11) and this is connected with part number if part number is same then 7XB should increment by 1 like 7XB1. I have all the months and day code but don’t know how to use it to generate these code and sequence in SQL server and also I can save in database. is there any way to write a query in sql which can decode the date if part number match with the given date.