Skip to content

Includes several functions to make it easier for you to work with Persian dates in SQL Server (2005+)

License

Notifications You must be signed in to change notification settings

mirsaeedi/SQLCLR-Jalali-Date-Utility

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

28 Commits
 
 
 
 
 
 
 
 
 
 

Repository files navigation

SQL CLR Jalali Date Time Utility

The aim of this project is to make it more easier for developers to work with jalali (shamsi - شمسی) dates.

💡 Installation

  • Make sure you have .NET framework 4.7.2+ installed on your server.

  • Since this library has beed developed using SQL CLR, firstly you need to configure your SQL Server by Enabling CLR Integration In SQL Server

SQL Server 2005-2014

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'clr enabled', 1;  
GO
RECONFIGURE;  
GO  

SQL Server 2017+

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'clr enabled', 1;  
GO
EXEC sp_configure 'clr strict security',0;
GO
RECONFIGURE;  
GO  
  • Run the Deployment Script in SQL Server Management Studio to generate the Jalali functions in your database.

  • Note that you have to run this script in SQLCMD Mode. So in SQL Management Studio, Select Query Menu, and then select SQLCMD Mode. Now you will be able to execute the query.

  • Please be sure that you have changed the [your-database-name] inside the Deployment Script to your database's name.

🕹 Usage

★ Convert Gregorian To Jalali:

Suppose that GETDATE() Method in sql server returns 2016/09/22 15:04:33

select dbo.GregorianToJalali(GETDATE(),'yy') -- returns 95
select dbo.GregorianToJalali(GETDATE(),'yyyy') -- returns 1395
select dbo.GregorianToJalali(GETDATE(),'yyyy-MM') -- returns 1395-07
select dbo.GregorianToJalali(GETDATE(),'yyyy-M') -- returns 1395-7
select dbo.GregorianToJalali(GETDATE(),'yyyy/MM/dd') -- returns 1395/07/01
select dbo.GregorianToJalali(GETDATE(),'yyyy/MM/dd hh:mm tt') -- returns 1395/07/01 03:04 ب ظ
select dbo.GregorianToJalali(GETDATE(),'yyyy/MM/dd hh:mm:ss tt') -- returns 1395/07/01 03:04:33 ب ظ
select dbo.GregorianToJalali(GETDATE(),'yyyy/MM/dd HH:mm') -- returns 1395/07/01 15:04
select dbo.GregorianToJalali(GETDATE(),'yyyy MMMM dddd') -- returns 1395 پنج شنبه مهر
★ Convert Jajali Date To Gregorian:
select dbo.JalaliToGregorian('95-06-11','-') --returns 2016-09-01 00:00:00.000
select dbo.JalaliToGregorian('1395/06/11','/') --returns 2016-09-01 00:00:00.000
★ Some times you need to have the first and last day of a persian month in gregorian date (specially in reporting)
select dbo.GetJalaliLastDayOfMonth(GETDATE()) --returns 2016-10-21 00:00:00.000 which is equal to 1395/07/30
select dbo.GetJalaliFirstDayOfMonth(GETDATE()) --returns 2016-09-22 00:00:00.000 which is equal to 1395/07/01

About

Includes several functions to make it easier for you to work with Persian dates in SQL Server (2005+)

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published