mibuso.com

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

format_date(<datefield>,'MM/DD/YYYY')

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

and

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.

thats a great post/tip. sadly i could have used that 3 weeks ago when I had to build a similar function from scratch

Great tip, thanks for sharing.

PS. IMHO posts like this is exactly what community and forums are all about.

Off course in SQL Server 2008R2 you got the date2 datatype whitout the hours

That's great - but NAV doesn't use Date2 (yet), so the issue with NAV tables continues for awhile...

Ron

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.