Nina Nina - 12 days ago 7
SQL Question

convert Date and find day of date

i want to convert date format and find day of date.

current Table Result :

| DATE1 | DATE2 |
-----------------------------------------------------
| 2016-06-10 00:00:00.000 | |
| 2014-07-11 00:00:00.000 | 7/11/2014 8:29:00 AM |
| 2015-06-01 00:00:00.000 | 6/1/2015 12:00:00 AM |
-----------------------------------------------------


if DATE2 is null then it should be same as DATE1.

Also defalut time for DATE2 is 12.00.00 i want another column weekday which display no. of day in week starting from sunday.

eg. DATE2 = 7/11/2014 it comes on friday so weekday = '6' and day='Friday'

Expected Result :

| DATE1 | DATE2 |weekDay | Day |
-------------------------------------------------------------
| 20160610 |2016-06-10 12:00:00.000 | 6 | Friday |
| 20140711 |2014-07-11 12:00:00.000 | 6 | Friday |
| 20150601 |2015-06-01 12:00:00.000 | 2 | Monday |
-------------------------------------------------------------

Answer

Try This.

For display no of day from date use :

            DATEPART(day,datecolumn)

For dispaly day of date Use :

            datename(dw,datecolumn)

Total query for your table like this.

            select convert(varchar(20),date1,112) as Date,

            coalesce(dateadd(HOUR,12,date2),dateadd(HOUR,12,date1)) as Date1,

            DATEPART(day,coalesce(date2,date1))as WeekDay 

            ,datename(dw,coalesce(date2,date1)) as Day

            from YourTable