Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
"Nicely" formatted dates in SQL queries
rsaritzky
Member Posts: 469
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.
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.
Ron
0
Comments
Epimatic Corp.
http://www.epimatic.com
PS. IMHO posts like this is exactly what community and forums are all about.
Ron