Identiy in a SmalldateTime Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identiy in a SmalldateTime Field

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2006-03-08 : 07:34:35

Hi.

Since i can not have a Identity column declared as DateTime,
how can i do to create a Table Like this
i give the StartDate and EndDate (DMY) 01/01/2006 - 15/01/2006

Table Tablex

Iddate (Smalldatetime - Key) Next(int) Prev(int)
01/01/2006 4 5
02/01/2006 4 5
03/01/2006 4 5
..
..
15/01/2006 4 5

tks again
C.Lages

CLages
Posting Yak Master

116 Posts

Posted - 2006-03-08 : 09:06:52

i solved by my self in this way, if there are a easy way
let me know
tks
C. Lages

set dateformat dmy
declare @Dtini Smalldatetime,
@Dtfim Smalldatetime,

@Dup1 Numeric(1,0),
@Dup2 Numeric(1,0),
@Dup3 Numeric(1,0),
@Dup4 Numeric(1,0),
@Dup5 Numeric(1,0),
@Dup6 Numeric(1,0),
@Dup7 Numeric(1,0),

@Tit1 Numeric(1,0),
@Tit2 Numeric(1,0),
@Tit3 Numeric(1,0),
@Tit4 Numeric(1,0),
@Tit5 Numeric(1,0),
@Tit6 Numeric(1,0),
@Tit7 Numeric(1,0)

set @dtini = '01/01/2006'
set @dtfim = '31/01/2006'

set @Dup1 = 1
set @Dup2 = 2
set @Dup3 = 3
set @Dup4 = 4
set @Dup5 = 5
set @Dup6 = 6
set @Dup7 = 7

set @Tit1 = 7
set @Tit2 = 6
set @Tit3 = 5
set @Tit4 = 4
set @Tit5 = 3
set @Tit6 = 2
set @Tit7 = 1

SET NOCOUNT ON

truncate table TabFluxo

BEGIN
while @dtini <= @dtfim
begin

insert into TabFluxo
select @dtini ,
CASE
WHEN DATENAME(weekday, @dtini) = 'Sunday' THEN @Dup1
WHEN DATENAME(weekday, @dtini) = 'Monday' THEN @Dup2
WHEN DATENAME(weekday, @dtini) = 'Tuesday' THEN @Dup3
WHEN DATENAME(weekday, @dtini) = 'Wednesday' THEN @Dup4
WHEN DATENAME(weekday, @dtini) = 'Thursday' THEN @Dup5
WHEN DATENAME(weekday, @dtini) = 'Friday' THEN @Dup6
WHEN DATENAME(weekday, @dtini) = 'Saturday' THEN @Dup7
END,
CASE
WHEN DATENAME(weekday, @dtini) = 'Sunday' THEN @Tit1
WHEN DATENAME(weekday, @dtini) = 'Monday' THEN @Tit2
WHEN DATENAME(weekday, @dtini) = 'Tuesday' THEN @Tit3
WHEN DATENAME(weekday, @dtini) = 'Wednesday' THEN @Tit4
WHEN DATENAME(weekday, @dtini) = 'Thursday' THEN @Tit5
WHEN DATENAME(weekday, @dtini) = 'Friday' THEN @Tit6
WHEN DATENAME(weekday, @dtini) = 'Saturday' THEN @Tit7
END

set @dtini = Dateadd(day ,1, @dtini)
end
END

GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-08 : 10:03:46

Why do you want to do this?
Where do you want to show the data?

Madhivanan

Failing to plan is Planning to fail

CLages
Posting Yak Master

116 Posts

Posted - 2006-03-14 : 10:06:14

I will not show anywhere, this is a Auxiliar table

if i have a Invoice date like this (DAY/MONTH/YEAR)

05/03/06 Sunday
06/03/06 monday
...

10/03/06 Friday
11/03/06 Satuday

and in this table i have this

Date Day_to_add

05/03/06 2
06/03/06 1
...

10/03/06 4
11/03/06 3

Every time I get a data i will check and add #Day_to_add to
Invoice date

this is because hOLLIDAY, weekEnd, etc, for Bank account Purpose.

C. Lages

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-14 : 19:01:44

this might also help http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-15 : 01:12:34

Also refer http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources