To GET Names of days between two days in mysql

Question

Is there any method in MySql which can return the name of days between two dates. For example 06/11/207 is start date and 12/11/2017 is end date then I need to get the output as:

monday,tuesday,wednesday,thursday,friday,saturday,sunday

Answer 1

you can try this :

you can take F_TABLE_NUMBER_RANGE function from this link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

declare @start_date     datetime
declare @end_date       datetime
declare @days           int

select @start_date  = '06/11/2017'
select @end_date    = '12/11/2017'

select @days = datediff(dd,@start_date,@end_date) +1

select
    [Date]      = dateadd(dd,number-1,@start_date),
    [Day of Week]   = datename(weekday,dateadd(dd,number-1,@start_date))
from
    dbo.F_TABLE_NUMBER_RANGE( 1, @days )
order by
    number

This I have tried and working for me cheers !

Answer 2

tl;dr

Use Java. Auto-localize.

myResultSet.getObject( … , LocalDate.class )   // Retrieve a `LocalDate` object from your database.
    .getDayOfWeek()                            // Fetch the `DayOfWeek` object appropriate for this date. Ex: DayOfWeek.MONDAY
    .getDisplayName(                           // Generate a String of the name of day-of-week.
        TextStyle.FULL ,                       // How long or abbreviated?
        Locale.US                              // Or Locale.CANADA_FRENCH, etc.
    )

Monday

Details

Generally best to do such transformations within your app rather than SQL, IMHO.

Java has an industry-leading date-time framework now in Java 8 and later, the java.time classes.

The LocalDate class represents a date-only value without time-of-day and without time zone.

With JDBC 4.2 and later, you can directly exchange java.time objects with your database.

LocalDate localDate = myResultSet.getObject( … , LocalDate.class ) ;  // Retrieval.
myPreparedStatement.setObject( … , localDate ) ; // Insert/Update.

The DayOfWeek enum offers seven existing objects, one for each day of the week. Use the getDisplayName method to generate an automatically localized name for the day-of-week.

DayOfWeek dow = localDate.getDayOfWeek() ;
String output = dow.getDisplayName( TextStyle.FULL , Locale.CANADA_FRENCH ) ;

lundi

Or, US English:

DayOfWeek dow = localDate.getDayOfWeek() ;
String output = dow.getDisplayName( TextStyle.FULL , Locale.US ) ;

Monday

Compare LocalDate objects with isBefore and isAfter and other methods. Loop each date incrementally by calling LocalDate::plusDays.

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.