Windows Batch Scripting: Get the previous date of a given date

Hello everyone

It’s been great working with windows batch scripting. Just to get all of us familiar with windows batch scripting, let us take up a small date play activity.
Let’s name the activity as “GetPreviousDateInteractive”.
The intention is to create a batch file which
1.       Displays the previous date immediately when opened
2.       Interacts with us asking if we wanna play more
3.       If yes, then asks us to enter year, month and date and gives us the previous date
4.       If no, it simply exits
Note you that this is not some kind of a random pick but the very idea of the activity has its vitality in most of the requirements playing with the date. I therefore believe that this would be useful to us from learning perspective and boosts our confidence to do a hands on work.

@ECHO OFF
REM - Grab the year, month and date from the system date
SET /A y=%date:~10,4%  
SET /A m=%date:~4,2%
SET /A d=%date:~7,2%

SET /A origdate=d
REM - If the system date is January first, set year to previous year
IF %d%==1 IF %m% ==1 SET /A y-=1
REM – If month of the system date is greater than January, then set month to previous month
IF %d%==1 IF %m% GTR 01 SET /A m-=1
REM – If month of the system date is January, then set month to december
IF %d%==1 IF %m% ==1 SET /A m=12

REM – Calculate the modulus of the year to determine if it is a leap year or not

SET /A  rmdr=(y%%4+4)%%4

REM – If the date is first of a month set the last date of previous month

IF %d%==1 IF %m%==1 SET /A d=31
IF %d%==1 IF %m%==2 IF %rmdr%==0 SET /A d=29 REM – If the modulus calculated above is zero, the year is leap, so february will have 29 days
IF %d%==1 IF %m%==2 IF %rmdr% NEQ 0 SET /A d=28 REM – If the modulus calculated above is not zero, the year is not leap, so february will have 28 days
IF %d%==1 IF %m%==3 SET /A d=31
IF %d%==1 IF %m%==4 SET /A d=30
IF %d%==1 IF %m%==5 SET /A d=31
IF %d%==1 IF %m%==6 SET /A d=30
IF %d%==1 IF %m%==7 SET /A d=31
IF %d%==1 IF %m%==8 SET /A d=31
IF %d%==1 IF %m%==9 SET /A d=30
IF %d%==1 IF %m%==10 SET /A d=31
IF %d%==1 IF %m%==11 SET /A d=30
IF %d%==1 IF %m%==12 SET /A d=31

REM – If the date is not equal to one, reduce the date by one
IF %origdate% NEQ 1 SET /A d=%origdate%-1
REM – If the month or date is less than  ten, append a zero at the beginning, so that there will be a two digit format
IF %m% LSS 10 set m=0%m%
IF %d% LSS 10 set d=0%d%

REM – Recreate the previous date with the year_month_date attributes

SET timeperiod=%y%_%m%_%d%
ECHO Yesterday's date is %timeperiod%

REM – Make the script more interactive with the labels and go to in the below code

:Play
SET /p q=Do you wanna play more?
IF %q%==yes GOTO :MorePlay
IF %q%==Yes GOTO :MorePlay
IF %q%==s GOTO :MorePlay
IF %q%==S GOTO :MorePlay
IF %q%==No GOTO :EOF
IF %q%==no GOTO :EOF
IF %q%==N GOTO :EOF
IF %q%==n GOTO :EOF

:MorePlay
SET /p y=Enter the year:
SET /p m=Enter the month:
SET /p d=Enter the date:

SET /A y=%y%

SET /A m=%m%
SET /A d=%d%

SET /A origdate=d

IF %d%==1 IF %m% ==1 SET /A y-=1
IF %d%==1 IF %m% GTR 01 SET /A m-=1
IF %d%==1 IF %m% ==1 SET /A m=12

SET /A  rmdr=(y%%4+4)%%4

IF %d%==1 IF %m%==1 SET /A d=31
IF %d%==1 IF %m%==2 IF %rmdr%==0 SET /A d=29
IF %d%==1 IF %m%==2 IF %rmdr% NEQ 0 SET /A d=28
IF %d%==1 IF %m%==3 SET /A d=31
IF %d%==1 IF %m%==4 SET /A d=30
IF %d%==1 IF %m%==5 SET /A d=31
IF %d%==1 IF %m%==6 SET /A d=30
IF %d%==1 IF %m%==7 SET /A d=31
IF %d%==1 IF %m%==8 SET /A d=31
IF %d%==1 IF %m%==9 SET /A d=30
IF %d%==1 IF %m%==10 SET /A d=31
IF %d%==1 IF %m%==11 SET /A d=30
IF %d%==1 IF %m%==12 SET /A d=31

IF %origdate% NEQ 1 SET /A d=%origdate%-1
IF %m% LSS 10 set m=0%m%
IF %d% LSS 10 set d=0%d%

SET timeperiod=%y%_%m%_%d%
ECHO Previous Day Date is %timeperiod%

GOTO :Play

Try this yourself. Meet you in the next post.

Thanks
SunilDutt N

SQLServer : Reading flat data stored in XML column

Hello everyone,
It took me a while to get on to XML data in SQL Server. I recently encountered a scenario of reading flat data from an XML column.
Another scenario of reading data from an XML saved in a VARCHAR column.

Scenario01: Reading XML data from an XML value saved in the XML type column
So, as a first step, lets create a BookSpace table and save some XML data in it.

CREATE TABLE dbo.BookSpace (ID INT IDENTITY(1,1), BookSpaced XML)
GO
INSERT INTO dbo.BookSpace (BookSpaced)
SELECT CONVERT(XML, '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Author>
<AuthorName>Mokkapati NarasimhaSastry</AuthorName>
<AuthorNationality>Indian</AuthorNationality>
</Author>
<Book>
<BookName>Barrister Parvateesam</BookName>
<OriginalLanguage>Telugu</OriginalLanguage>
<YearPublished>1924</YearPublished>
</Book>
</BookShelf>'
) AS Book
UNION ALL
SELECT CONVERT(XML, '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Author>
<AuthorName>Chilakamarthi Lakshmi Narasimham</AuthorName>
<AuthorNationality>Indian</AuthorNationality>
</Author>
<Book>
<BookName>Ganapathi</BookName>
<OriginalLanguage>Telugu</OriginalLanguage>
<YearPublished>1920</YearPublished>
</Book>
</BookShelf>'
) AS Book
UNION ALL
SELECT CONVERT(XML, '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Author>
<AuthorName>Charles Dickens</AuthorName>
<AuthorNationality>English</AuthorNationality>
</Author>
<Book>
<BookName>Great Expectations</BookName>
<OriginalLanguage>English</OriginalLanguage>
<YearPublished>1861</YearPublished>
</Book>
</BookShelf>'
) AS Book
UNION ALL
SELECT CONVERT(XML, '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Author>
<AuthorName>Charles Dickens</AuthorName>
<AuthorNationality>English</AuthorNationality>
</Author>
<Book>
<BookName>A Christmas Carol</BookName>
<OriginalLanguage>English</OriginalLanguage>
<YearPublished>1843</YearPublished>
</Book>
</BookShelf>'
) AS Book
UNION ALL
SELECT CONVERT(XML, '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Author>
<AuthorName>Dan Brown</AuthorName>
<AuthorNationality>American</AuthorNationality>
</Author>
<Book>
<BookName>The Da Vinci Code</BookName>
<OriginalLanguage>English</OriginalLanguage>
<YearPublished>2003</YearPublished>
</Book>
</BookShelf>'
) AS Book
UNION ALL
SELECT CONVERT(XML, '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Author>
<AuthorName>Miguel de Cervantes Saavedra</AuthorName>
<AuthorNationality>Spanish</AuthorNationality>
</Author>
<Book>
<BookName>The Ingenious Gentleman Don Quixote of La Mancha</BookName>
<OriginalLanguage>Spanish</OriginalLanguage>
<YearPublished>1605</YearPublished>
</Book>
</BookShelf>'
) AS Book
GO
SELECT * FROM dbo.BookSpace (NOLOCK)
GO

We can read flat data from XML column as below:
SELECT BookSpaced.value('(BookShelf/Book/BookName)[1]','VARCHAR(100)') AS BookName
, BookSpaced.value('(BookShelf/Book/OriginalLanguage)[1]','VARCHAR(50)') AS OriginalLanguage
, BookSpaced.value('(BookShelf/Book/YearPublished)[1]','INT') AS YearPublished
, BookSpaced.value('(BookShelf/Author/AuthorName)[1]','VARCHAR(250)') AS AuthorName
, BookSpaced.value('(BookShelf/Author/AuthorNationality)[1]','VARCHAR(50)') AS AuthorNationality
FROM dbo.BookSpace (NOLOCK)
 
 

Now, our goal is to read the XML data as a flat data.
For that, the below query will help us. I gave the color notation in the query so that we can quickly get what attribute is used where.

SELECT BookSpaced.value('(BookShelf/Book/BookName)[1]','VARCHAR(100)') AS BookName
, BookSpaced.value('(BookShelf/Book/OriginalLanguage)[1]','VARCHAR(50)') AS OriginalLanguage
, BookSpaced.value('(BookShelf/Book/YearPublished)[1]','INT') AS YearPublished
, BookSpaced.value('(BookShelf/Author/AuthorName)[1]','VARCHAR(250)') AS AuthorName
, BookSpaced.value('(BookShelf/Author/AuthorNationality)[1]','VARCHAR(50)') AS AuthorNationality
FROM dbo.BookSpace

Our XML value is as below:

'<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Author>
    <AuthorName>Miguel de Cervantes Saavedra
    <AuthorNationality>Spanish
  </Author>
  <Book>
    <BookName>The Ingenious Gentleman Don Quixote of La Mancha
    <OriginalLanguage>Spanish
    <YearPublished>1605
  </Book>
</BookShelf>'

In other words, it is

SELECT  XMLColumnName.value(  ‘(Node/SubNode1/Subnode2/…/FinalNode)[1]’,  ’Data Type’   ) AS OurAlias

FROM

Just to note that here the keyword “value” should be in all small letters as it is actually the value() method that performs XQuery against XML and returns data in the specified SQL datatype.

XMLColumn.value(XQuery, SQLType)

XQuery is the XQuery expression i.e. a string literal, that retrieves data inside the XML instance or can also be considered as node by node navigation till the data value.

SELECT BookSpaced.value('(BookShelf/Book/BookName)[1]','VARCHAR(100)') AS BookName

FROM dbo.BookSpace

In this example, XQuery is  '(BookShelf/Book/BookName)[1]'

SQLType is  'VARCHAR(100)'

Scenario02: Reading XML data from an XML saved in the varchar type column

So, as a first step, lets create a TextBookSpace table and save some XML data in it.

CREATE TABLE dbo.TextBookSpace (ID INT IDENTITY(1,1), BookSpaced VARCHAR(1000))
GO
INSERT INTO dbo.TextBookSpace (BookSpaced)
SELECT '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Author>
    <AuthorName>Mokkapati NarasimhaSastry</AuthorName>
    <AuthorNationality>Indian</AuthorNationality>
  </Author>
  <Book>
    <BookName>Barrister Parvateesam</BookName>
    <OriginalLanguage>Telugu</OriginalLanguage>
    <YearPublished>1924</YearPublished>
  </Book>
</BookShelf>'
AS Book
UNION ALL
SELECT '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Author>
    <AuthorName>Chilakamarthi Lakshmi Narasimham</AuthorName>
    <AuthorNationality>Indian</AuthorNationality>
  </Author>
  <Book>
    <BookName>Ganapathi</BookName>
    <OriginalLanguage>Telugu</OriginalLanguage>
    <YearPublished>1920</YearPublished>
  </Book>
</BookShelf>'
AS Book
UNION ALL
SELECT '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Author>
    <AuthorName>Charles Dickens</AuthorName>
    <AuthorNationality>English</AuthorNationality>
  </Author>
  <Book>
    <BookName>Great Expectations</BookName>
    <OriginalLanguage>English</OriginalLanguage>
    <YearPublished>1861</YearPublished>
  </Book>
</BookShelf>'
AS Book
UNION ALL
SELECT '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Author>
    <AuthorName>Charles Dickens</AuthorName>
    <AuthorNationality>English</AuthorNationality>
  </Author>
  <Book>
    <BookName>A Christmas Carol</BookName>
    <OriginalLanguage>English</OriginalLanguage>
    <YearPublished>1843</YearPublished>
  </Book>
</BookShelf>'
AS Book
UNION ALL
SELECT '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Author>
    <AuthorName>Dan Brown</AuthorName>
    <AuthorNationality>American</AuthorNationality>
  </Author>
  <Book>
    <BookName>The Da Vinci Code</BookName>
    <OriginalLanguage>English</OriginalLanguage>
    <YearPublished>2003</YearPublished>
  </Book>
</BookShelf>'
AS Book
UNION ALL
SELECT '<BookShelf xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Author>
    <AuthorName>Miguel de Cervantes Saavedra</AuthorName>
    <AuthorNationality>Spanish</AuthorNationality>
  </Author>
  <Book>
    <BookName>The Ingenious Gentleman Don Quixote of La Mancha</BookName>
    <OriginalLanguage>Spanish</OriginalLanguage>
    <YearPublished>1605</YearPublished>
  </Book>
</BookShelf>'
AS Book
SELECT * FROM dbo.TextBookSpace (NOLOCK)

In this case, we can convert the VARCHAR column to XML type and follow the same process like using value() method to read the XML data.

SELECT BookSpaced.value('(BookShelf/Book/BookName)[1]','VARCHAR(100)') AS BookName

, BookSpaced.value('(BookShelf/Book/OriginalLanguage)[1]','VARCHAR(50)') AS OriginalLanguage
, BookSpaced.value('(BookShelf/Book/YearPublished)[1]','INT') AS YearPublished
, BookSpaced.value('(BookShelf/Author/AuthorName)[1]','VARCHAR(250)') AS AuthorName
, BookSpaced.value('(BookShelf/Author/AuthorNationality)[1]','VARCHAR(50)') AS AuthorNationality
FROM (SELECT ID, CONVERT(XML,BookSpaced) AS BookSpaced FROM dbo.TextBookSpace (NOLOCK)) AS Q

But mind you, sometimes, we may encounter strange situations of NULL data being resulted from the above query.

To handle those, we need to consider using substrings and charindex to get the data value we need.

For example, if we want to get the BookName and AuthorName, we can use the below query

SELECT SUBSTRING(BookSpaced,CHARINDEX('<BookName>',BookSpaced)+LEN('<BookName>'),CHARINDEX('</BookName>',BookSpaced) - CHARINDEX('<BookName>',BookSpaced)-LEN('<BookName>')) AS BookName
, SUBSTRING(BookSpaced,CHARINDEX('<AuthorName>',BookSpaced)+LEN('<AuthorName>'),CHARINDEX('</AuthorName>',BookSpaced) - CHARINDEX('<AuthorName>',BookSpaced)-LEN('<AuthorName>')) AS AuthorName
FROM dbo.TextBookSpace

 

To explain this query, for BookName column,

In our xml column the value is like …..<BookName>Ganapathi</BookName>…….

We are using substring function to achieve this. We gave the column name as the first parameter.

The second parameter is position from where the substring should be started.
We need to read the book name Ganapathi, for that we are finding the character position that starts with and subtracting the length of from it. Let us say, we got the value B1.
Note that can be replaced with any other node in the xml.

The third parameter is length to which the substring should be read. Here it is dynamic, we can’t guess the length of a book name.

So, we need to get the length of bookname dynamically. i.e. the starting position of the end node
MINUS the second parameter calculated.

With this we are at the closure of the discussion.

Will be meeting you soon with another scenario.

Thanks
SunilDutt N