"Nicely" formatted dates in SQL queries

rsaritzkyrsaritzky Posts: 347Member
edited 2012-03-27 in SQL Tips & Tricks
I would expect that many of us wish formatting dates (to remove the "time" portion of the date field) in Transact-SQL or other queries to the NAV database was easier. I found that a commonly-quoted solution of using

convert(varchar, <datefield> ,101)

causes problems in Excel if you are querying the SQL databae directly from Excel - the above function converts the date to a varchar (text) field in Excel, so you can't sort by date if your standard date format is dd/mm/yyyy - all the "January" dates sort together regardless of year.

Well, I received an email from Database Journal today, and they had a link to the following article

http://www.databasejournal.com/features ... r-2008.htm

The contributor published a function that converts dates to date fields with formats similar to the way NAV formats work (not exactly but it reminded me of NAV). For example, to return a date in format mm/dd/yyyy, the function


returns the date field as a date field in the format MM/DD/YYYY

Similarly, you can pull any part of the date off, e.g.

format_date(<datefield>,'MM') returns just the month


format_date(<datefield>,'MONTH') returns the name of the month.

Lots of formats in this great function!:
YYYY - Year in YYYY Format including century
Yr - Year in YY format
QQ - Display Quarter
MM - Display Month
WW - Diplay Week
DD - Display day
24HH - Display hour in 24 hr format
12HH - Display hour in 12 hr format
MI - Display minutes
SS - Display seconds
MS - Display Milliseconds
MCS - Display MicroSeconds
NS - Display NanoSeconds
DAY - Display day name example: Monday
MONTH- - Display Month name example: August
MON - Display short month name example: Aug
AMPM - Display AM / PM for 12 hr format
TZ - Display time offset
UNIXPOSIX - Display unix posix time. Number of seconds from 1/1/1970 (disabled in this version)
UCASE - Display the result in upper case
LCASE - Display the result in lower case

CAVEAT: The UNIXPOSIX format (number of seconds since 1/1/1970) doesn't work for dates prior to 12/14/1901, so 'empty' dates in NAV that are stored as 1/1/1753 cause the function to fail. I simply removed 2 lines of code to eliminate that format since I had no use for it. There are also 3 other formats that work only in SQL 2008, so if you're running SQL 2005 or earlier you have to remove another 6 lines from the function. Just read the full comment thread below the article and it will document the changes you need to make.


  • jlandeenjlandeen Posts: 524Member
    thats a great post/tip. sadly i could have used that 3 weeks ago when I had to build a similar function from scratch :(
    Jeff Landeen - Sr. Consultant
    Epimatic Corp.

  • AdministratorAdministrator Posts: 2,209Member, Moderator, Administrator
    [Topic moved from SQL General to SQL Tips & Tricks forum]
  • David_SingletonDavid_Singleton Posts: 5,364Member
    Great tip, thanks for sharing. =D>

    PS. IMHO posts like this is exactly what community and forums are all about.
    David Singleton
    Dynamics NAV Freelancer
    Dynamics Book
    Go-Live International
  • ResenderResender Posts: 119Member
    Off course in SQL Server 2008R2 you got the date2 datatype whitout the hours
  • rsaritzkyrsaritzky Posts: 347Member
    That's great - but NAV doesn't use Date2 (yet), so the issue with NAV tables continues for awhile...

  • ResenderResender Posts: 119Member
    rsaritzky wrote:
    That's great - but NAV doesn't use Date2 (yet), so the issue with NAV tables continues for awhile...

    I haven't run Nav on sql server (yet), so does it mean that you can use the datatype but nav doesn't recognise it.
Sign In or Register to comment.