0

Well, my problem is that I want only to show the date and time for each column and I don't really know how to do it. The DB I'm using is not mine and I'm not allowed to change the data type of the field...

Here is my query:

SELECT CAT_COURT.COURT_DESCRIPTION, AGENDA.SCHEDULE_DATE, AGENDA.SCHEDULE_TIME 
FROM AGENDA JOIN CAT_COURT ON AGENDA.ID_COURT=CAT_COURT.ID_COURT 
WHERE SCHEDULE_DATE = CAST(GETDATE() AS DATE) AND CAT_COURT.ID_COURT=CAT_COURT.ID_COURT

And here is the result:

| COURT_DESCRIPTION |       SCHEDULE_DATE      |       SCHEDULE_TIME     |   
|-------------------+--------------------------+------------------------ |  
|       COURT1      | 2016-07-22 00:00:00.000  | 1900-01-01 10:00:00.000 | 
|       COURT2      | 2016-07-22 00:00:00.000  | 1900-01-01 10:00:00.000 |
|       COURT3      | 2016-07-22 00:00:00.000  | 1900-01-01 12:00:00.000 |  
|       COURT4      | 2016-07-22 00:00:00.000  | 1900-01-01 13:30:00.000 |

But I want to show something like this:

| COURT_DESCRIPTION | SCHEDULE_DATE | SCHEDULE_TIME |   
|-------------------+---------------+---------------|  
|       COURT1      |  2016-07-22   | 10:00:00.000  | 
|       COURT2      |  2016-07-22   | 10:00:00.000  |
|       COURT3      |  2016-07-22   | 12:00:00.000  |  
|       COURT4      |  2016-07-22   | 13:30:00.000  |

Can anyone help me?

Phantom_strike
  • 149
  • 2
  • 10
  • Does this help you? http://stackoverflow.com/questions/889629/how-to-get-a-date-in-yyyy-mm-dd-format-from-a-tsql-datetime-field – Chasen Bettinger Jul 23 '16 at 04:00
  • There are several similar questions on SO about this -- here's one for the time: http://stackoverflow.com/questions/7710449/how-to-get-time-from-datetime-format-in-sql; here's one for the date: http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype – sgeddes Jul 23 '16 at 04:01
  • I already tried those solutions but I don't know how to do it in my own query :/ – Phantom_strike Jul 23 '16 at 04:04
  • @Phantom_strike for more formats http://www.sql-server-helper.com/tips/date-formats.aspx – Arun Prasad E S Jul 23 '16 at 04:14

1 Answers1

0

Simple Date Conversions will give desired Result 

       SELECT CAT_COURT.COURT_DESCRIPTION,
        convert(varchar(10),AGENDA.SCHEDULE_DATE,120)SCHEDULE_DATE,
         CONVERT(VARCHAR(8),AGENDA.SCHEDULE_DATE, 108)SCHEDULE_Time
          FROM AGENDA 
         JOIN CAT_COURT 
         ON AGENDA.ID_COURT=CAT_COURT.ID_COURT 
         WHERE 
         SCHEDULE_DATE = CAST(GETDATE() AS DATE) AND CAT_COURT.ID_COURT=CAT_COURT.ID_COURT
mohan111
  • 8,633
  • 4
  • 28
  • 55