Sql query to get the arabic date for the gregorian date from a database

Question

I have a table with columns Hijri date and Gregorian date. I want a select query to fetch the Hijri date from this table by passing the Gregorian date. I will pass the Gregorian date as a parameter from my code in C#.

Answer 1

SqlServer can convert Gregorian date into Hijri by using the CONVERT function and supplying the appropriate style 130 or 131. See also the MSDN documentation.

Assuming you have stored your dates in Gregorian calendar in your database you can use this to get the Hijri date:

DateTime start = ...
DateTime end = ...

...

var cmd = new SqlCommand("SELECT CONVERT(varchar, YourDateColumn, 131) FROM YourTable WHERE YourDateColumn BETWEEN @startdate AND @enddate");
cmd.Parameters.AddWithValue("@startdate", start);
cmd.Parameters.AddWithValue("@enddate", end);

If you want something else you need to be more specific.

The answer to this question shows some examples of how to perform datetime convertions with Hijri in .NET.