If an EZWriter Data Source returns date/time fields from Enterprise, you will want to format how the date/time displays on the EZWriter report (for example mm/dd/yyyy). Formatting of dates must be done in the EZWriter Data Source with SQL. The Enterprise fields cannot be formatted in the EZWriter Data Format window.
Below are examples of SQL that format dates/times:
SELECT CONVERT(VARCHAR, GETDATE(),101) --RETURNS MM/DD/YYYY
SELECT CONVERT(VARCHAR, GETDATE(),105) --RETURNS MM-DD-YYYY
SELECT CONVERT(VARCHAR, GETDATE(),107) --RETURNS Mon DD, YYYY
SELECT RIGHT(GETDATE(),7) --RETURNS H:MM AM/PM
Comments
6 comments
how can we return the month to a full month as opposed to Jan, Feb etc
0 upvotes
SELECT DATENAME(MONTH, GETDATE())
1 upvotes
Thanks Lee
0 upvotes
I would like to filter for only the events of the next 14 days, but somehow the EZWriter Data Source does not display a result on WRI. Does anyone have a tip on how to combine the SQL for CONVERT, DATEADD and GETDATE ?
FROM EV200_EVENT_MASTER
WHERE (EV200_EVT_END_DATE>CONVERT(DATE,DATEADD(DAY,14,GETDATE())))
AND (EV200_EVT_START_DATE<=CONVERT(DATE,GETDATE()))
When I set the EVT_END_DATE to >CONVERT(DATE,GETDATE())) all events from today are displayed.
Thanks!
0 upvotes
I believe you have you greater than and less then backwards
FROM EV200_EVENT_MASTER
WHERE (EV200_EVT_END_DATE<CONVERT(DATE,DATEADD(DAY,14,GETDATE())))
AND (EV200_EVT_START_DATE>=CONVERT(DATE,GETDATE()))
0 upvotes
Hi Dustin, thanks for spotting this. Now it works!
0 upvotes
Please sign in to leave a comment.