mibuso.com

Microsoft Business Solutions online community
It is currently Mon Apr 21, 2014 1:28 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: "Nicely" formatted dates in SQL queries
PostPosted: Wed Jun 03, 2009 1:06 am 
Offline

Joined: Wed Jun 20, 2001 7:01 am
Posts: 239
Location: Los Angeles, CA
Country: United States (us)
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.

_________________
Ron


Top
 Profile E-mail  
 
 Post subject: Re: "Nicely" formatted dates in SQL queries
PostPosted: Wed Jun 03, 2009 4:44 pm 
Offline

Joined: Tue May 22, 2007 7:15 pm
Posts: 514
Location: Toronto
Country: Canada (ca)
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.

http://www.epimatic.com


Top
 Profile E-mail WWW  
 
 Post subject: Re: "Nicely" formatted dates in SQL queries
PostPosted: Sun Jun 14, 2009 12:01 pm 
Offline
Site Admin

Joined: Sun Nov 07, 1999 8:01 am
Posts: 2017
Location: Wilrijk, Belgium
Country: Belgium (be)
[Topic moved from SQL General to SQL Tips & Tricks forum]


Top
 Profile  
 
 Post subject: Re: "Nicely" formatted dates in SQL queries
PostPosted: Sun Jun 14, 2009 2:58 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
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


Top
 Profile E-mail WWW  
 
 Post subject: Re: "Nicely" formatted dates in SQL queries
PostPosted: Mon Mar 05, 2012 12:35 pm 
Offline

Joined: Tue Oct 04, 2011 10:26 am
Posts: 94
Country: Belgium (be)
Off course in SQL Server 2008R2 you got the date2 datatype whitout the hours


Top
 Profile E-mail  
 
 Post subject: Re: "Nicely" formatted dates in SQL queries
PostPosted: Mon Mar 05, 2012 6:10 pm 
Offline

Joined: Wed Jun 20, 2001 7:01 am
Posts: 239
Location: Los Angeles, CA
Country: United States (us)
That's great - but NAV doesn't use Date2 (yet), so the issue with NAV tables continues for awhile...

Ron


Top
 Profile E-mail  
 
 Post subject: Re: "Nicely" formatted dates in SQL queries
PostPosted: Tue Mar 27, 2012 10:34 am 
Offline

Joined: Tue Oct 04, 2011 10:26 am
Posts: 94
Country: Belgium (be)
rsaritzky wrote:
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.


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: