Journey So Far With Blogging

I had never thought even in my dreams that I would be blogging some technical contents.

Trust me, now I feel that I should have started blogging as soon as I started my carrier, I would have had thousands of personal bookmarks that I could have shared with the technical community.

How did the idea Crop up?

Well it all started with “BING” and “GOOGLE” … J

For a developer those two search engines are like Life Savers or you could say Job savers J

Whenever I used to search for something in SQL I used to get SQLauthority.com, amazing content, point to point information and the topics worth getting into your bookmarks. Seriously speaking that was the site which motivated me to start my own blog.

After 3 months of blogging I feel that I have my personal bookmarks which are open to all and many people view the site on a daily basis which is a great motivation – thanks a lot for all the people who do see the blog.

Please key in your comments and feedbacks to further improve the blog and give some quality content to the technical community.

Thanks a lot for reading so far.

Here are some of the stats of the blog:

Country-wise views recorded till 22 May 2013

Generate Emails and Store it in a File system C#

Today I was working on a small but interesting application for creating .eml files and storing it as a draft in file system.

This can be done by writing a small set of code as shown below:

//Create a mail message object

//Parameter 1 : From Email ID

//Parameter 2 : To Email ID

//Parameter 3: Email Subject

//Parameter 4: Email Body

 

MailMessage message = new MailMessage(“fromEmail@Test.com”, “ToEmail@test.com”, “EmailSubject”, “Email Body”);

 

//Add this header so that the when you double click on .eml file it opens as draft email

message.Headers.Add(“X-Unsent”, “1″);

 

//In case there is attachment present add it like the below code (optional)

message.Attachments.Add(new Attachment(attachmentLocation));

 

SmtpClient client = new SmtpClient(“mysmtphost”);

 

//Make sure the delivery method is selected to specifiedPickUpDirectory

client.DeliveryMethod = SmtpDeliveryMethod.SpecifiedPickupDirectory;

 

//Save the generated emails into a specified location

client.PickupDirectoryLocation = @”D:\GeneratedEmails”;

 

client.Send(message);

 

Once you generate the emails, the .eml files will be saved in the draft format in the location specified.

NOTE : The naming convention of the emails generated in the file system cannot be changed as it will follow the default naming convention of outlook.

Hope you find the blog post interesting.

Signing off Anish.

Common issue faced when querying SQL DB with GETDATE() : Part 2

This blog post is a continuation to the blog that I had written earlier on the common issues of Date time in SQL.

If you have missed out on reading the Part -1, here is the link to the blog

Common issue faced when querying SQL DB with GETDATE() : Part 1

Let’s take the same case scenario as explained in the above link,

Run the below query to get the sample table ready,

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblCalender](

[ID] [int] IDENTITY(1,1) NOT NULL,

[FinancialYear] [nvarchar](4) NULL,

[FinancialMonth] [int] NULL,

[FromDate] [datetime] NULL,

[ToDate] [datetime] NULL,

CONSTRAINT [PK_tblCalender] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

After you run the table script, run the below query to get the values into the table,

SET IDENTITY_INSERT [dbo].[tblCalender] ON

INSERT [dbo].[tblCalender] ([ID], [FinancialYear], [FinancialMonth], [FromDate], [ToDate]) VALUES (1, N’FY14′, 1, CAST(0x0000A19300000000 AS DateTime), CAST(0x0000A1B000000000 AS DateTime))

INSERT [dbo].[tblCalender] ([ID], [FinancialYear], [FinancialMonth], [FromDate], [ToDate]) VALUES (2, N’FY14′, 2, CAST(0x0000A1B100000000 AS DateTime), CAST(0x0000A1CF00000000 AS DateTime))

SET IDENTITY_INSERT [dbo].[tblCalender] OFF

After you run this script, change the system date to 30-Apr-2013.

Our basic motive here is to get the financial year from the database comparing the present date in the database tables. Now run the below query,

select FinancialYear FROM tblCalender where getdate() BETWEEN FromDate and ToDate

If you run the query you can see that the query yields no result.

Get Date With Old Data in Calender Table

Now instead of manipulating the query part as in the earlier blog we will change the way the values are entered in the database.

Delete the values present in the database by running the below script and insert new values into the database.

Run the below query keeping the system date 30-Apr-2013.

 

Truncate table tblCalender

 

SET IDENTITY_INSERT [dbo].[tblCalender] ON

 

INSERT [dbo].[tblCalender] ([ID], [FinancialYear], [FinancialMonth], [FromDate], [ToDate])

VALUES (1, N’FY14′, 1, ’2013-04-01 00:00:00.000′,’2013-04-30 23:59:59.000′)

 

INSERT [dbo].[tblCalender] ([ID], [FinancialYear], [FinancialMonth], [FromDate], [ToDate])

VALUES (2, N’FY14′, 2, ’2013-05-01 00:00:00.000′,’2013-05-31 23:59:59.000′)

 

After you insert the new values in the table run the below query

select FinancialYear FROM tblCalender where getdate() BETWEEN FromDate and ToDate

You can see that the same query yields the required result from the database.

GETDATE With New Data

So the question that comes up is, what was the difference in the old and new data that was inserted in to the table ?

The difference was, in the date field of ToDate the old value was “2013-04-30 00:00:00.000” and the new value is “2013-04-30 23:59:59.000”. There was a difference of 24 hours that was missing in the value which was the reason for variation of the results.

Hope you liked the blog post.

Would love to hear if you have faced similar kind of situations, were because of the wrong entry in the datetime field there is a difference in the output of a query.

 Thanks for taking time and reading. 

 Signing Off Anish.