It’s very common to show the current date (i.e., month, day, and year) in different formats. Although you can obtain the current date with Microsoft SQL Server’s GETDATE function, getting the returned date in the format you need can take a lot of effort and code. The FormatDate UDF (user-defined function) provides dates in various formats by using the following options:
- mm = month
- dd = day
- yyyy or yy = year
- hh = hours
- nn = minutes
- ss = seconds
- ms = milliseconds
- mmmm = long month name (e.g., January)
- mmm = short (three characters) month name (e.g., Jan)
- wdd = long day-of-the-week name (e.g., Monday)
- wd = short (three characters) day-of-the-week name (e.g., Mon)
- AM/PM = AM or PM
- am/pm = am or pm
- A/P = A or P
- a/p = a or p
So if you want to format the current system date as dd/mm/yyyy, you’d specify:
SELECT dbo.FormatDate(GETDATE(),’dd/mm/yyyy’)
To get this result ’14/07/2012 14:07:00′ you would use the following statement:
SELECT dbo.FormatDate(‘1:45 pm 27 Mar 07′, mm/dd/yyyy hh:nn:ss’)
Hope this will help you. Happy Formatting!
No Responses