Want to generate report

  • Good day! I want to get only the date part of a value from a datetime stored in the database. The values stored in that column are in this format mm/dd/yyyy hh:mm tt. How can I use the distinct sql keyword to return unique date? In order for me to do that, I need to know how to get only the date part, excluding the time. Thank you so much!

    Column name:Job_Time

    value of JOb_Time:2016-01-27 08:46:51.493

    I want only date @date=Convert(varchar,STUDENT_CRS_HIST.JOB_TIME,110)

    When I am using this formula I am getting error.

    Do you have any idea to solve this by another way?

    Any help would be appreciated.

    Thanks

  • Have you tried CAST(STUDENT_CRS_HIST.JOB_TIME AS DATE)?

    The below query would give you a list of distinct dates.

    SELECT DISTINCT CAST(STUDENT_CRS_HIST.JOB_TIME AS DATE) AS JOB_DATE

    FROM STUDENT_CRS_HIST

    For an alternative, check out the FORMAT function - it's a very useful alternative to CAST/CONVERT when you're trying to convert a date or datetime to NVARCHAR/VARCHAR.

    SELECT DISTINCT FORMAT(STUDENT_CRS_HIST.JOB_TIME, 'MM-dd-yyyy') AS JOB_DATE_AS_NVARCHAR

    FROM STUDENT_CRS_HIST

  • Andrew P (5/9/2016)


    Have you tried CAST(STUDENT_CRS_HIST.JOB_TIME AS DATE)?

    The below query would give you a list of distinct dates.

    SELECT DISTINCT CAST(STUDENT_CRS_HIST.JOB_TIME AS DATE) AS JOB_DATE

    FROM STUDENT_CRS_HIST

    For an alternative, check out the FORMAT function - it's a very useful alternative to CAST/CONVERT when you're trying to convert a date or datetime to NVARCHAR/VARCHAR.

    SELECT DISTINCT FORMAT(STUDENT_CRS_HIST.JOB_TIME, 'MM-dd-yyyy') AS JOB_DATE_AS_NVARCHAR

    FROM STUDENT_CRS_HIST

    The FORMAT function is horrendously slow (comparatively). Futhermore, CASTing from DATETIME to DATE is SARGable, whereas FORMAT is not.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (5/10/2016)


    Andrew P (5/9/2016)


    Have you tried CAST(STUDENT_CRS_HIST.JOB_TIME AS DATE)?

    The below query would give you a list of distinct dates.

    SELECT DISTINCT CAST(STUDENT_CRS_HIST.JOB_TIME AS DATE) AS JOB_DATE

    FROM STUDENT_CRS_HIST

    For an alternative, check out the FORMAT function - it's a very useful alternative to CAST/CONVERT when you're trying to convert a date or datetime to NVARCHAR/VARCHAR.

    SELECT DISTINCT FORMAT(STUDENT_CRS_HIST.JOB_TIME, 'MM-dd-yyyy') AS JOB_DATE_AS_NVARCHAR

    FROM STUDENT_CRS_HIST

    The FORMAT function is horrendously slow (comparatively). Futhermore, CASTing from DATETIME to DATE is SARGable, whereas FORMAT is not.

    Drew

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply