Hijri Date in SQL Server - with Islamic Month Name

This article focuses on converting the Gregorian date into an Islamic date along with Islamic month name in textual format. In SQL Server there are two methods to convert the Gregorian date to Islamic date, however, for Islamic month name we need to depend on our own code.

Either you can write a function or a stored procedure or you can simply put the month names in your query's conditional clauses.

First, let me show you the methods, SQL Server supports -

SELECT GETDATE() AS [GregorianDate],

       CONVERT(VARCHAR(23),GETDATE(),131) AS [Hijri date]

GO

SELECT GETDATE() AS [GregorianDate], 

       FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss','ar') AS [Hijri date]

GO

In case you need to display the Islamic date in text format like "11 al-Muḥarram 1442" -

SELECT

CONVERT(VARCHAR(2), DATEPART(DAY, FORMAT(GETDATE(),'yyyy-MM-dd','ar'))) + ' ' +

CONVERT(NVARCHAR(30), CASE (DATEPART(MONTH, FORMAT(GETDATE(),'yyyy-MM-dd','ar')))

    WHEN 1 THEN N'al-Muḥarram'

    WHEN 2 THEN N'Ṣafar'

    WHEN 3 THEN N'Rabīʿ al-ʾAwwal'

    WHEN 4 THEN N'Rabīʿ ath-Thānī'

    WHEN 5 THEN N'Jumādā al-ʾAwwal'

    WHEN 6 THEN N'Jumādā ath-Thāniyah'

    WHEN 7 THEN N'Rajab'

    WHEN 8 THEN N'Shaʿbān'

    WHEN 9 THEN N'Ramaḍān'

    WHEN 10 THEN N'Shawwāl'

    WHEN 11 THEN N'Zū al-Qaʿdah'

    WHEN 12 THEN N'Zū al-Ḥijjah'

    END) + ' ' +

CONVERT(VARCHAR(4), DATEPART(YEAR, FORMAT(GETDATE(),'yyyy-MM-dd','ar')))

GO

Do let me know if you find any other way. 

Moving a table from one Hive database to another

This article is to let you know how to move a table from one database to another in Hive.

CREATE TABLE TestDB.emp 
AS
SELECT * FROM dbTest.emp;

The above statement will create a table (named "emp") in the database in which you want to move the table and import the data from the old one. You can keep your old table in the old database or you can simply drop it.

If you do not want to use the "create" statement, then you have a choice to alter the table location as mentioned below.

ALTER TABLE TestDB.emp RENAME TO dbTest.emp;

Hope it helps..!!

XML in SQL Server - Part-2

In the previous part, we learned how to use the default modes and how we can change the shapes of the XML model using the same available modes. We will learn how to read the XML file that is generated by SQL Server.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData

FOR XML RAW;

Once you execute your query, you will see the results in the result-pane, which can be saved as CSV file, however, it shouldn’t be the right way to save XML files that generated by SQL Server.

Click on the result from the result-pane. The entire XML document gets opened in a new window. Go to File and select “Save As”. It will prompt you the file-directory to save the file based on your choice.

Once the file is saved, open a new query window and type the below command –

SELECT CAST(XMLData AS XML)
FROM OPENROWSET(
       BULK 'D:\MyDocuments\blog posts\XML-SQLServer\test.xml'

       , SINGLE_BLOB) AS X(XMLData)

Please do let me know if you need more clarification.

XML in SQL Server

This is my second post on XML after 10 years. I was contented with the earlier post however I experienced more over these years and learned many things which I wanted to share with the current and future generations.

Let me begin with the basics.

XML is a file extension for an Extensible Markup Language (XML) represents information in a hierarchical (tree) structure in a simple text format. An XML document consists of XML elements and all elements in an XML document can contain sub-elements, text, or attributes. The hierarchical structure represented by an XML document starts at the root element and branches to the lowest level of elements.

There are several modes that change the shape of XML format while generating it through SQL Server using FOR XML clause.

Instead of reinventing the wheel, I would quote the MSDN information here for the descriptions with the examples I prepared.

Here is the data for the tryout.

VideoID VideoDesc vcVideoLocation
842735 SQL D:\Partition23\20200101\73899mpart
842736 Java   D:\Partition23\20200101\74900mpart
842738 XML D:\Partition23\20200101\74901mpart
842739 Hive D:\Partition23\20200101\74901mpart
842740 Sqoop D:\Partition23\20200101\74900mpart
842742 Impala D:\Partition23\20200101\73899mpart
842743 SQL D:\Partition23\20200101\74900mpart
842744 Hadoop D:\Partition23\20200104\74791mpart
842745 SQL D:\Partition23\20200101\74901mpart
842746 Hive D:\Partition23\20200101\73899mpart
842747 Sqoop D:\Partition23\20200104\74791mpart
842748 Impala D:\Partition23\20200101\74900mpart
842749 Spark D:\Partition23\20200101\74900mpart
842750 HBase D:\Partition23\20200101\73899mpart
842751 Scala D:\Partition23\20200104\74791mpart
842752 Hive D:\Partition23\20200104\74791mpart
842753 SQL D:\Partition23\20200101\74901mpart
842756 Impala D:\Partition23\20200101\74901mpart

SQL Code:

CREATE TABLE VideoData(VideoID BIGINT, VideoDesc VARCHAR(10), vcVideoLocation NVARCHAR(MAX))

INSERT INTO VideoData(VideoID, VideoDesc, vcVideoLocation) VALUES
(842735,'SQL','D:\Partition23\20200101\73899mpart'),
(842736,'Java','D:\Partition23\20200101\74900mpart'),(842738,'XML','D:\Partition23\20200101\74901mpart'),(842739,'Hive','D:\Partition23\20200101\74901mpart'),(842740,'Sqoop','D:\Partition23\20200101\74900mpart'),(842742,'Impala','D:\Partition23\20200101\73899mpart'),(842743,'SQL','D:\Partition23\20200101\74900mpart'),(842744,'Hadoop','D:\Partition23\20200104\74791mpart'),(842745,'SQL','D:\Partition23\20200101\74901mpart'),(842746,'Hive','D:\Partition23\20200101\73899mpart'),(842747,'Sqoop','D:\Partition23\20200104\74791mpart'),(842748,'Impala','D:\Partition23\20200101\74900mpart'),(842749,'Spark','D:\Partition23\20200101\74900mpart'),(842750,'HBase','D:\Partition23\20200101\73899mpart'),(842751,'Scala','D:\Partition23\20200104\74791mpart'),(842752,'Hive','D:\Partition23\20200104\74791mpart'),(842753,'SQL','D:\Partition23\20200101\74901mpart'),(842756,'Impala','D:\Partition23\20200101\74901mpart')

XML RAW:

Each row in the result set is taken as one element with your columns being the attributes. 

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML RAW;

If you look at the output, each row from the table is represented as a single element in XML and is also represented by the keyword <row>. As stated in the beginning, the elements can contain sub-elements, text or attributes; here the table’s columns are being treated as attributes. What if the keyword ELEMENTS mentioned after FOR XML RAW? Let’s check it out. 

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML RAW, ELEMENTS;

Now, each row element has column names as sub-elements. 

FOR XML RAW and FOR XML RAW, ELEMENTS are returning a shape that is slightly different.

XML AUTO:

Returns query results in a simple, nested XML tree. Each table in the FROM clause for which at least one column is listed in the SELECT clause is represented as an XML element. 

Each row of the table will be represented as an element by table name.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML AUTO; 

Let’s add “ELEMENTS” to it and see how the output changes.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML AUTO, ELEMENTS;

Table name as an element for each row and each element has column names as sub-elements

 

XML PATH:

Provides a simpler way to mix elements and attributes, and to introduce additional nesting for representing complex properties. Table columns are passed as child elements.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML PATH;

If you look at the outcome of “FOR XML RAW, ELEMENTS” and “FOR XML PATH” there is no difference at all. Both are returning the same elements and sub-elements. 

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML PATH, ELEMENTS; 

Again, the output is as same as “FOR XML RAW, ELEMENTS” and “FOR XML PATH”.

Look at the below example -

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML PATH('VideoInfo');

User can replace the default keyword “row” with any desired information. In the above example, since the data is related to Videos, I mentioned “VideoInfo” for the readability. It solves no other purpose.

Synopsis: RAW will return the table’s row as an element and column names being the attributes. AUTO will return the table’s name as an element and the element contains the entire row and the column names are the attributes. PATH returns the row as element and columns as child or sub-elements.

Now let’s go back to the data; the table contains video information such as on which topic the video is created and where the video is located. There is more than one video on the same subject and there are several videos on the same location. 

We can create different shapes apart from the above by using the existing modes, either clustered/grouped by topic of the video or by the location, similar to bitmap fashion. Look at the below to understand easily.

SELECT vcVideoLocation AS '@path', (
              SELECT VideoID AS [index], VideoDesc FROM VideoData t1
              WHERE t1.vcVideoLocation = t2.vcVideoLocation
              FOR XML PATH('VideoInfo'),TYPE
                                                          )
              FROM (
                      SELECT DISTINCT vcVideoLocation FROM VideoData) t2
                      FOR XML PATH('vLocation'), ROOT('data') 

“data” is the main element for the whole table data, the location is sub-element; and in each location, there are several videos that are being treated as attributes within the user-defined sub-element “VideoInfo”. Look at the results below.

In the above example, the data is grouped by “location”. Similarly, we can shape it by grouping by “Subject” (i.e. Video Description).

SELECT VideoDesc AS '@Desc', (
              SELECT VideoID AS [index], vcVideoLocation FROM VideoData t1
              WHERE t1.VideoDesc = t2.VideoDesc
              FOR XML PATH('VideoInfo'),TYPE
                                                  )
              FROM (
                      SELECT DISTINCT VideoDesc FROM VideoData) t2
                      FOR XML PATH('VideoType'), ROOT('data')

Output: 

Hope you find this article helpful.