Posted by Erland Sommarskog on 08/20/06 17:05

rcamarda (robc390@hotmail.com) writes:
> Sorry about the insert, i see that its returning what I did not expect.
> Originally I had the dates quoted, but sql bawked at that. I've been
> trying to fix the insert, but after trying cast and convert, it still
> wont go.
> This gives error about conversion:
> INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS

Yes, the above format could fail. There are three date formats in SQL
Server that are safe:

YYYYMMDD
YYYYMMDDTHH:MM:SS[.fff]
YYYY-MM-DDZ

Here T and Z stand for themselves.

Other formats are interpretated depending on DATEFORMAT and LANGUAGE
setting, and can fail or produced unexpected results if you don't know
what is going on.

> Im still trying to grasp the use of the numbers table. I have a D_Day
> table that is the days from 1900 - 2100. Could that be used somehow?
> (1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)

Yes, that dates table is essentially a table of numbers with a different
names. In fact, it appears that it has all the numbers as well!

I used a table of numbers, as numbers is the more general concept and
can be used in more places. But in fact, I added a table of dates to
our system before I added a table of numbers.

I leave it as an exercise to you how to use the dates table instead.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]