Chicken and egg

Hi there,

This strikes me as odd but I was trying to create a NULL datetime value for storage in a SQL server table and found that a .NET datetime object defaults to its min value of Jan 1, 1 A.D. OK, now this in itself is not a problem, but the min value for a date in SQL server is Jan 1 1753, so if I just try to save the default value .NET datetime, I get a value out of range exception. Annoying but reasonably easy to remedy.

I was just wondering if anyone knew which min value came first, why, and why the second min value, or at least its default value, wasn't made equal to the other.

Cheers, DanYou could just give your input parameter in SQL a default of NULL and if your date in your .NET code is 1/1/1 then do NOT add a parameter for it (so your NULL default will be used).

Checksum computation help

Please execute the script below to understand the problem -

--
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

--id & col1 make up the PK.

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')

select *
from test

select *
from test2

--The rows are identical.
--Script A

select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)

--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)

-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.

TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')

--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.

Thanks

drop table test
drop table test2
go
--Script B is not correct because you have no keys in tables and, of course, it returns rows - col3s are different. There is relation many to many.|||And did you look up CHECKSUM() in BOL?

I know you're trying to accomplish something...but you got me lost..

It's in the same manner as your previous threads...

Can you give us a "big picture" view of what you're trying to accomplish?

I don't mean to offend, but you need to understan what primary keys are for...sounds like your data model is not fitting in quite right with what you're trying to accomplish...|||I think this would give you an idea of the data. Yesterday when I did the processing I had this view of the table -

ID...County...Univ...Dept.....Status

1...A......XYZ...Accounting...Processed - Good
1...A......ABC...Accounting...Processed - Bad
1...A......XYZ...Marketing...Processed - Good
1...B......PQR...HR............Processed - Good
1...C......XXX...HR............Processed - Bad

I have an index on the Status field coz I can see all Bad records on top.

Today I have in my source system -

ID...County...Univ...Dept

1...A......ABC...Accounting
1...A......XYZ...Accounting
1...A......XYZ...Marketing
1...B......PQR...HR
1...C......XXX...HR
2...C......YYY...Training

I want to process only those records that are new/updated since yesterday's version. I get the above records in a separate table and assign a Status to them as 'Not Processed'. I then compare the two tables. And so because of the problem stated before, I end up processing a record that I have processed the previous day.

So how do I go about this problem? Is there a need for another column in here.

Checking the datatype of a columns

Hello,

I have the following sql statement

Code Snippet

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

I am converting a nvarchar to a datetime on one of the columns in the table. However, I don't want to execute this if the conversion has already executed.

I was thinking of having a if statement that if the column is not a datetime then alter the column.

I am unsure how to write the if statement to check for the data type of that column.

Many thanks for any help,

Steve

If you're intention is to ultimately convert the datatype, what's preventing you from initially setting the datatype as datetime?

I know this doesn't answer your question, but I'm curious.

Adamus

|||Hello,

The table was created initially with a nvarchar. This is a live database and the dates that have already been entered have to be formated in order for the alter column will work.

If the customer runs this script more than once, I don't want to have to execute the alter statement again.

Many thanks,

Steve
|||

There's really not a problem if that statement executes EVEN if the datatype has been previously changed.

However, if you need to:

Code Snippet

IF NOT EXISTS
( SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ( TABLE_NAME = 'OtherCall'
AND COLUMN_NAME = 'Date'
AND DATA_TYPE = 'datetime'
)
)
BEGIN
UPDATE OtherCall

SET [Date] = convert( nvarchar(50), convert( datetime, [Date], 103), 111)

ALTER TABLE OtherCall ALTER COLUMN [Date] datetime

END

|||

Try the below SQL Statement, this is my version of solution there may be other ways to

' Returns row if the column is already converted to the required data type

IF EXISTS (select c.name, c.xtype from sysobjects o, syscolumns c where o.id = c.id and o.name = tablename and c.xtype= 61(xtype value of datatime datatype, can be found in systypes table in master database) and c.name =column name)
BEGIN
// The column is already converted to datatime format. In your case this step will be blank
END
ELSE

BEGIN

// The column is not in datatime datatype so we need to convert it. Below is your code for conversion

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

END

Checking for where a datetime column has a null value

I'm trying to filter out sales reps in a resultset that are no longer
with a company, whereas 'Start' is when the salesrep was hired ( a
datetime datatype ) and "Terminated' is when a salesrep was
terminated ( a datetime datatype).
Nulls are allowed in the Terminated column. If that column is null,
then a salesrep still is employed. But when I want to include it in a
where clause:
WHERE (QueriedDate = '20050707') AND (SalesReps.Terminated =
'null'), the syntax is invalid . What function can i use in this sql
string to temporarily change 'Terminated' into something useable so
that I can check for nulls in that column.
Thanks
.NetSportsNULL is not a string, it marks the absence of a value (e.g. UNKNOWN). And
you cannot expect a sane answer from "is terminated = unknown?" Try
AND Terminated IS NULL
"netsports" <ballz2wall@.cox-dot-net.no-spam.invalid> wrote in message
news:EomdnRFBorvv9ZfeRVn_vQ@.giganews.com...
> I'm trying to filter out sales reps in a resultset that are no longer
> with a company, whereas 'Start' is when the salesrep was hired ( a
> datetime datatype ) and "Terminated' is when a salesrep was
> terminated ( a datetime datatype).
> Nulls are allowed in the Terminated column. If that column is null,
> then a salesrep still is employed. But when I want to include it in a
> where clause:
> WHERE (QueriedDate = '20050707') AND (SalesReps.Terminated =
> 'null'), the syntax is invalid . What function can i use in this sql
> string to temporarily change 'Terminated' into something useable so
> that I can check for nulls in that column.
> Thanks
> NetSports
>