[thelist] Using Wildcards and Dates in SQL Server

Ken Schaefer ken at adOpenStatic.com
Tue Jun 17 21:26:13 CDT 2003


Just to add to rudy's very correct post.

DateTimes are stored as two integers. The first representing the number of
days before or after an arbitrary starting base date (1 January 1900), and
the second representing the number of milliseconds after midnight.

You can do the following in Query Analyser to see:
SELECT CAST(0 AS DateTime)
SELECT CAST(0 AS SmallDateTime)

Everything else, (eg '2003/16/06 2:00:00 AM) needs to be implicitly cast,
which requires SQL Server to be able to parse the string to work out what
date/time you actually mean.

Understanding this makes 99% of errors that people have manipulating
date/time fields go away, since you start to understand that your string
needs to be formatted in a way that SQL Server understands, not how the
client tools might display these underlying integers to you.

Cheers
Ken

----- Original Message ----- 
From: "rudy" <r937 at interlog.com>
To: <thelist at lists.evolt.org>
Sent: Wednesday, June 18, 2003 8:10 AM
Subject: Re: [thelist] Using Wildcards and Dates in SQL Server


: > The dates are stored in a single column called "eDate" in the
: > 'mm/dd/yyyy' format. I thought about doing something like "select where
: > left(eDate,2) = 6" but I can't always guarantee that the month is two
: > digits. SQL Server stores January 1st as 1/1/2003 instead of 01/01/2003,
: > and I don't think there's any way in SQL to catch both cases.
:
: actually, this is a very common misperception
:
: datetime values are not stored in sql server as date strings, they are
: stored as integers which represent the number of days since some base date
: (january 1 1753, i believe)
:
: whenever you display a date, sql server automatically converts it for you,
: using default formatting
:
: you can select a different format with the CONVERT function --
:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp



More information about the thelist mailing list