CODEWITHSUNDEEP
sqlsql-serversql-server-2016
JM1
JM1

Reputation: 1715

SQL Server determining the day from an existing date column

I'm trying to determine what day a date is by looking at a column date. Is it possible, and if so, how do you look at a column date/reference the column, and determine which day it is?

Current results:

enter image description here

Desired results:

enter image description here

Code for testing:

 CREATE TABLE #Date 
 (
     IncidentDate DATE,
     [Day] VARCHAR(10)
 )

 INSERT INTO #Date (IncidentDate)
 VALUES ('20170803'), ('20170817'), ('20170831'), ('20170901'),
        ('20170905'), ('20170914'), ('20170908'), ('20170914'), 
        ('20171010'), ('20171020'), ('20171024'), ('20171027'),
        ('20171026'), ('20171024'), ('20171102'), ('20171106')

SELECT *
FROM #Date

Upvotes: 2

Views: 109

Answers (5)

Andy K
Andy K

Reputation: 5054

you mean something like this 

 SELECT IncidentDate,DATENAME(dw,IncidentDate)
 FROM #Date

Dbfiddle here

Explanation can be found here

Upvotes: 1

bastos.sergio
bastos.sergio

Reputation: 6764

You can use the function DATENAME, like so...

SELECT DATENAME(dw, IncidentDate)
FROM #Date

The first input of the function dw can vary, based on the date part that you want to get out of the date (month, day, quarter, year, etc...)

More information here

Upvotes: 1

yoyoyoyo123
yoyoyoyo123

Reputation: 2472

you can use DATENAME in building the table - note the conversion of the string first.

CREATE TABLE #Date (
 IncidentDate DATE,
 [Day] varchar(10)
 )

 INSERT INTO #Date
 (
     IncidentDate,
     Day
 )
 VALUES
 ('20170803', DATENAME(dw,CONVERT(CHAR(10), '20170803', 120)))


 SELECT *
 FROM #Date

outout

IncidentDate    Day
2017-08-03      Thursday

Upvotes: 2

Steven
Steven

Reputation: 911

The DATENAME function will return the name of the date.

Using your above data:

 SELECT   *
        , DATENAME(dw, IncidentDate) [DayOfWeek]
 FROM #Date

Upvotes: 2

jose_bacoy
jose_bacoy

Reputation: 12704

You can use datename function in sqlserver.

  SELECT DATENAME(dw,'2018/04/13');

  Answer: Friday

Trivia: Today is friday the 13th.

Upvotes: 2

Related Questions