Phantom_strike Phantom_strike - 4 months ago 6
SQL Question

SQL Server 2008: Get date/time from datetime

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?

Answer

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