May 9, 2016 at 3:39 pm
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
May 9, 2016 at 5:09 pm
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
May 10, 2016 at 8:16 am
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_DATEFROM 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_NVARCHARFROM 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
May 10, 2016 at 3:54 pm
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_DATEFROM 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_NVARCHARFROM 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