Late June .NET News

Late June .NET News

Posted by md3 on Thursday, June 24, 2004 (PST)

News articles and links found in .NET Emails

Date and Time Manipulation in SQL Server 2000 Tell a Friend

By Manuj Bahl
Published: 6/8/2004 Reader Level: Beginner Intermediate
Rated: 4.67 by 3 member(s).
Discuss in the Forums Printable Version Rate this Article

SQL Server 2000 does not have separate data types for date and time. Instead the Microsoft SQL Server Team chose to combine both the data types into one and store it as a datetime data type. Date and time can be stored in SQL Server in datetime or smalldatetime. The datetime data type can store dates from January 1, 1753 to December 31, 9999 to an accuracy of up to 0.003 part of a second. The smalldatetime data type can store data from January 1, 1900 to June 6, 2079 with accuracy of up to the minute.

SQL Server takes into account a system reference date, which is called the base date for SQL Server. This base date is January 1st, 1900. It is from here that the main problem stems. SQL Server stores the datetime data type internally as two 4 byte integers and smalldatetime as two 2 byte integers. The first integer in both the cases stores the number of day difference from the base date. The second integer part stores the number of milliseconds/minutes since midnight.

Date and Time Data Entry
When only the time part is provided as input, the base date is appended to the time. If only the date part is provided the time appended is as of midnight. Some example code to observe the same is as follows:

use pubs

---------------- Inserting only the time part into a datetime column --------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go

/* Inserting the test value into the table */
insert into MyDateTest99 values ('10:00 AM')
go

/* Selecting the result */
select DateColumn from MyDateTest99
go

/* Performing Cleanup */
drop table MyDateTest99
go

---------------- Inserting only the date part into a datetime column --------------
use pubs

/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go

/* Inserting the test value into the table */
insert into MyDateTest99 values ('January 1, 2000')
go

/* Selecting the result */
select DateColumn from MyDateTest99
go

/* Performing Cleanup */
drop table MyDateTest99
go

So, the most common question that is asked is:
Q: How do I get SQL Server to return only the Date component or only the Time component from the datetime data type?
A: By using the Convert function. The syntax for using the convert function is:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

By varying the datatype and length, we can get the desired component. Moreover, the style argument in the Convert function is provided exclusively for use with date and time data. Some sample code illustrating the same is as follows:

use pubs

---------------- Selecting only the date part from a datetime column --------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go

/* Inserting the test value into the table */
insert into MyDateTest99 values (getdate())
go

/* Selecting the result */
select convert(varchar,DateColumn,101) from MyDateTest99
go

/* Performing Cleanup */
drop table MyDateTest99
go

use pubs

---------------- Selecting only the date part from a datetime column --------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go

/* Inserting the test value into the table */
insert into MyDateTest99 values (getdate())
go

/* Selecting the result */
select convert(varchar,DateColumn,108) from MyDateTest99
go

/* Performing Cleanup */
drop table MyDateTest99
go

The list of styles that can be used are:

Style ID
Style Type

0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM

These styles are the format of input to be used when converting character data into datetime and format of output while converting datetime data into characters:

use pubs

---------------- Example for the demonstration of use of style while input of data--------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go

/* Inserting the test values into the table */
-- Inserting in US format
insert into MyDateTest99 select convert(datetime,'05/08/2004',101)
-- Inserting in UK format
insert into MyDateTest99 select convert(datetime,'08/05/2004',103)
-- Inserting in ISO Format
insert into MyDateTest99 select convert(datetime,'20040508',112)
go

/* Selecting the result */
select DateColumn from MyDateTest99
go

/* Performing Cleanup */
drop table MyDateTest99
go

use pubs

---------------- Example for the demonstration of use of style while output of data--------------

/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test values into the table */
insert into MyDateTest99 select convert(datetime,'05/08/2004',101)

/* Selecting the result */
-- In US Format
select convert(varchar,DateColumn,101) from MyDateTest99
-- In UK Format
select convert(varchar,DateColumn,103) from MyDateTest99
-- In ISO Format
select convert(varchar,DateColumn,112) from MyDateTest99

go
/* Performing Cleanup */
drop table MyDateTest99
go

Some other functions that can be used for various purposes are DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE, MONTH, and YEAR. Here's some further detail on these functions as well as a code sample showing their use:

Dateadd: Returns a new datetime value based on adding an interval to the specified date.

Syntax: DATEADD ( datepart, number, date )

Datediff: Returns the number of date and time boundaries crossed between two specified dates.

Syntax: DATEDIFF ( datepart, startdate, enddate )

Datename: Returns a character string representing the specified datepart of the specified date.

Syntax: DATENAME ( datepart, date )

Datepart: Returns an integer representing the specified datepart of the specified date.

Syntax: DATEPART ( datepart, date )

Day: Returns an integer representing the day datepart of the specified date.

Syntax: DAY ( date )

Getdate: Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.

Syntax: GETDATE ( )

Month: Returns an integer that represents the month part of a specified date.

Syntax: MONTH ( date )

Year: Returns an integer that represents the year part of a specified date.

Syntax: YEAR ( date )

declare @datevar datetime
select @datevar = getdate()

/*Example for getdate() : getting current datetime*/
select getdate() [Current Datetime]

/*Example for dateadd : getting date 7 days from current datetime*/
select dateadd(dd, 7, @datevar) [Date 7 days from now]

/*Example for datediff : getting no of days passed since 01-01-2004*/
select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]

/*Example for datename : getting month name*/
select datename(mm, @datevar) [Month Name]

/*Example for datepart : getting week from date*/
select datepart(wk, @datevar ) [Week No]

/*Example for day : getting day part of date*/
select day (@datevar) [Day]

/*Example for month : getting month part of date*/
select month(@datevar) [Month]

/*Example for year : getting year part of date*/
select year(@datevar) [Year]

Now I will provide you with some code samples which you can use for various tasks. I will try to include as many examples I can think of, but this list is not exhaustive:

1. To find the first day of a month:

select dateadd(dd,-(day(DateColumn)-1),DateColumn)

2. To find last day of a month:

select dateadd(dd,-(day(dateadd(mm,1,DateColumn))),dateadd(mm,1,DateColumn))

3. To find birthdays in next seven days:

use pubs

/* Creating a Test Table */
Create Table MyDateTest99
(
Birthday datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 select convert (varchar(10),'19780129',120)
insert into MyDateTest99 select convert (varchar(10),'19670821',120)
insert into MyDateTest99 select convert (varchar(10),'19910112',120)
insert into MyDateTest99 select convert (varchar(10),dateadd(dd,2,getdate()),120)
insert into MyDateTest99 select convert (varchar(10),'19791016',120)

go
/* Selecting the result */
select
Birthday
from
MyDateTest99
where
datediff
(
   dd
   ,convert(datetime,'1900/'+cast(month(getdate()) as varchar)+'/'+cast (day(getdate()) as varchar),111)
   ,convert(datetime,'1900/'+cast(month(Birthday) as varchar)+'/'+cast (day(Birthday) as varchar),111)
) between 0 and 7
go
/* Performing Cleanup */
drop table MyDateTest99
go

4. Number of hours until weekend, that is until Friday at 5 PM (my favorite):

use pubs

Create function udf_Time_to_Weekend (@d1 datetime) returns datetime
as
begin
declare @d2 datetime
select @d2 = case when (datepart(hh,dateadd(dd,(7-datepart(dw,@d1)),@d1)) >= 17 and 7-datepart(dw,@d1) = 0)
    then dateadd(hh,17,convert(varchar(10),dateadd(dd,7,@d1),101))
    else dateadd(hh,17,convert(varchar(10),dateadd(dd,(7-datepart(dw,@d1)),@d1),101))
   end
return @D2
END
go
Create   procedure HoursTillWeekend as
set datefirst 6
select DATEDIFF(MI,GETDATE(),dbo.udf_Time_to_Weekend(getdate()))/60 "Hours Till Weekend"
go
exec HoursTillWeekend
go
drop procedure HoursTillWeekend
go
drop function udf_Time_to_Weekend
go

5. First and last days of quarter, in which a date falls:

use pubs
go
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 select convert (varchar(10),'19780129',120)
insert into MyDateTest99 select convert (varchar(10),'19670821',120)
insert into MyDateTest99 select convert (varchar(10),'19910112',120)
insert into MyDateTest99 select convert (varchar(10),'19791016',120)
go
/* Selecting the result */
select
datepart(qq,DateColumn) QuarterNo
,dateadd(qq,datepart(qq,DateColumn),dateadd(dd,-(datepart(dy,DateColumn)-1),DateColumn)) FirstDayOfQuarter
,dateadd(qq,datepart(qq,DateColumn)+1,dateadd(dd,-(datepart(dy,DateColumn)),DateColumn)) LastDayOfQuarter
from
MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go

6. Number of days in a month:

Create Function
udf_getNoOfDaysInMonth
(
@month int
,@year int
)
returns
int
as
begin
return datepart( dd,dateadd(dd,-1,(dateadd(mm,@month,dateadd( yyyy,@year-1900,'19000101')))))
end

select dbo.udf_getNoOfDaysInMonth(2,2004)

A very common question asked in forums is regarding the change from a character column to a datetime column. The error encountered by developers is :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is common because the varchar column does not provide any validations against the data and as a result, some invalid entries creep in. So, while converting to datetime, SQL Server is not able to change the character data to datetime and throws up an error. The easiest way to identify the rows that are causing problems and contain invalid datetime data is by using the isdate() function:

/* Example to show how to find invalid records */

use pubs
go
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn varchar(8)
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 select '19780129'
insert into MyDateTest99 select '19670229'
insert into MyDateTest99 select '19910112'
insert into MyDateTest99 select '19791016'
go
/* Selecting the result */
select
DateColumn
from
MyDateTest99
where
isdate(DateColumn) = 0
go
/* Performing Cleanup */
drop table MyDateTest99
go

Another common mistake made by developers is that while searching for all records on a particular day a where clause is used like "where logdate = @logdate", when they are passing @logdate as '01/01/2004'. '01/01/2004' really means '01/01/2004 00:00:00.000' and will not return data for the complete day. The problem can be solved by using the between clause. The where clause for such a query should be "where logdate between @logdate and @logdate2", where @logdate2 is @logdate + 1. The between clause can make use of an index if it exists, where using a convert function like "where convert(varchar,logdate,101) = @logdate" would not and it would slow down the query.

Please feel free to drop a e-mail and I will try to answer any queries you might have in my weblog or through e-mail. You can mail me at: enigma_chatzone@yahoo.com. My weblog is at http://www.sqljunkies.com/weblog/enigma.

Sponsored by:

©2000-2004 Atomic Consulting Group, Inc. All rights reserved.
Hosted By SecureWebs.com
Advertising Info | Privacy Policy | Terms of Service

Demystifying Object.GetHashCode (Part I)
As you probably already know, everything in .NET derives from System.Object. This ensures that all .NET objects support a common, minimal set of behaviors. However, it is not immediately obvious what all of the System.Object methods really do. In particular, the GetHashCode method seems to cause a lot of confusion among new .NET developers. While the purpose of the ToString method is fairly obvious, the utility of GetHashCode is far less intuitive. To understand its purpose, you must understand two concepts: what a hash algorithm does, and how hashing is typically applied in an application.

Hash Algorithms Defined
Simply and generically stated, the goal of a hash algorithm is to take a large chunk of data and convert it into a much smaller chunk of data called a hash code. But hashing is not the same as compression; you cannot expand the resulting hash code back into the original large chunk of data. Instead, the hash code simply “represents” the larger chunk.

In more specific terms, the Object.GetHashCode method returns an integer. In other words, the goal of this hash mechanism is to take all the data contained in an object and convert it to a 32 bit integer. At this point, you may be tempted to think of the hash code as an identifier. However, this is not entirely accurate either. The main problem with this interpretation is that there is no guarantee that a hash code is unique. For example, consider the following simple Person class:

class Person

{

private string mName;

private string mSSN;

}

This class has two string fields to hold the name and the Social Security number. The GetHashCode algorithm must process each field to generate a 32 bit integer. However, an integer has only so many different permutations – roughly about 4 billion and change (2 ^ 32 to be exact). Our Person object, on the other hand, has a much larger number of permutations (assuming Unicode character set: (16 ^ maximum string length) * 2). The bottom line is this: it is impossible to convert every possible Person object into a single unique integer value.

Although a truly unique value cannot be generated, it is possible to generate a relatively unique value. To understand what is meant by this, consider two questions:

You execute GetHashCode on two objects. The returned hash codes are the same. What can you say about these two objects?
You execute GetHashCode on two objects. The returned hash codes are different. What can you say about these two objects?

Lets look at the first question. If two objects return the same hash code, the most you can say about the two objects it that they may be equal. In fact, it is highly probable that they are equal, but you won’t know for sure unless you check each field (or call the Object.Equals method).

The second question addresses the most important characteristic of hash codes. If two objects return different hash codes then you know for a fact that the two objects are not equal. This characteristic can be used to implement efficient lookup mechanisms because you can focus the search on those objects that have the same hash code and skip equality processing on all objects that do not. And many times, the cost of computing a hash code is less than the cost of computing equality. Hash tables, in particular, leverage these hash code characteristics to implement an extremely efficient lookup mechanism. In fact, a hash table can often achieve O(1) efficiency on lookup, meaning it can find the requested object using only one comparison.

Implementing a Hash Algorithm
So now that you’ve seen the required characteristics of a hash code, the next obvious question is: how do I implement a hash algorithm? The implementation of hash algorithms is surprisingly complex and has been studied extensively in the world of computer science. For example, a hash algorithm that works fine in one context (like a hash table implementation) may be inadequate for another context (like security). Also of concern is the distribution of the hash algorithm. The goal is for the hash algorithm to generate relatively unique hash values for different chunks of data. If the hash algorithm tends to return the same hash value for different chunks of data, that is an example of an algorithm with a poor distribution. Some have written their doctorate thesis on such matters. In other words, “How do I implement a hash algorithm?” it is not an easy question to answer in the general case.

Lucky for us, we are not concerned about the general case. We are instead concerned about the specific case of the Person.GetHashCode method, which turns out to be surprisingly simple. First, it is important to note that the Person class contains an identity field – the SSN value. Hash algorithms usually act on all the fields contained in an object, but if your object contains an identity field (such as an SSN or a primary key from a database) then you can simply hash the identity field. Furthermore, the SSN field is declared as a String. As it happens, the .NET String class already implements an efficient hashing algorithm via (what else) the String.GetHashCode method. Therefore, the following Person.GetHashCode method simply calls GetHashCode on the SSN field and returns that result as the hash code for the entire Person object. Here is the code:

class Person

{

private string mName;

private string mSSN;

public override int GetHashCode()

{

return mSSN.GetHashCode();

}

The Next Step
In this tech tip, we’ve defined the goals of a hash algorithm, discussed some uses, and showed an example of overriding the Object.GetHashCode method. Demystifying Object.GetHashCode Part II will discuss focus on how to decide when you need to override GetHashCode, and provide additional guidelines regarding how to implement the algorithm.

Implementing NTLM Authentication for Your ASP.NET Web Services
Everybody knows that you can't prompt a user for authentication when accessing a Web service. But like many things that "everybody knows", it ain't necessarily so, as this article explains. (6/7/2004)

Upload a File using ASP.NET

The requirement of uploading a file to the web server is a requirement, which is, required a lot of web applications. We will look at how to do the same in this code tip.

ASP.NET makes it very easy to upload the required file to the web server. There are two pieces of code which are presented below:

HTML code
Code Behind Code