Coverting Int to DateTime Recommendation?

Friends,

I have a database that I am in the process of converting to a new application. All the dates are currently stored as an numeric YYYYMMDD format. I am hoping someone can suggest the best way of dealing with these in SQL Server. Should I convert them to DateTime formats for example, and what is the best way to do this?

Any input would be greatly appreciated.

J.H.

yes you should do it.

the best way?

this is how I would do it -

create function YYYMMDDToDate(@.date int)
returns datetime
as
begin
declare @.datestring char(8)
set @.datestring = cast(@.date as char(8))
return convert(datetime, substring(@.datestring,1,4) +'-' +
substring(@.datestring,5,2) +'-' +
substring(@.datestring,7,2), 127)
end
go
alter table affectedtable add newDateColumn datetime;
go
update affectedtable
set newDateColumn = dbo.YYYMMDDToDate(oldDateColumn);
alter table affectedtable drop column oldDateColumn;
exec sp_rename 'dbo.affectedtable.newDateColumn', 'oldDateColumn', 'COLUMN';

|||

The trick is that MSSQL recognizes dates in YYYYMMDD format unambigously. So may just convert it twice as:

select cast(cast(20060104 as varchar) as datetime)

Counting Groups

I'm trying to count the # of times a group header prints, but am having
little luck. If I create a data set with :
"Declare @.StartCount int
Set @.StartCount = 1
Select @.StartCount as Start"
Then pull the sum of the field into the group header, the very first number
it starts off with is 7, not 1. If I just pull the field itself, it returns
1 every time the group header repeats itself. If I don't use the set
statement, but set the input as a parameter with a default of 1, I get 1
every time the group repeats itself.
So, I went search on how to declare a variable as a counter and found a post
titled "Line Item Count" which contains part of the information I'm looking
for, but no details. David Siebert said:
"You could use custom code (under report properties) to increment a variable
for each row."
Unfortunately, not knowing VB very well, I have no idea how to implement
this. I tried doing a Dim statement in that window, then putting a textbox
on the line in question with an expression of "=Count(Counter + 1)", but I
keep getting a "Counter not declared" error message when I try to preview.
Can anyone help me out?
Thanks in advance,
Catadmin
--
MCDBA, MCSA
Random Thoughts: I only thought I was going crazy. I forgot I was already
there.Not sure why you are doing this but... anyway.
Put this code in your code section of report properties:
Public Shared gintCnt As Integer ' This will hold the count.
Public Shared Function IncDispCnt() As String
' This function will increment count and output result.
gintCnt += 1
Return gintCnt.ToString
End Function
Public Shared Function DisplayCnt() As String
' This function will output result.
Return gintCnt.ToString
End Function
Public Shared Function IncCnt() As String
' This function will increment count.
gintCnt += 1
Return ""
End Function
Public Shared Function ResetCnt() As String
' This function will increment count.
gintCnt = 0
Return ""
End Function
I gave you a few options there. Add a textbox to the header you want to
count and place this in the expression:
=Code.IncCnt()
In the page footer add a textbox and place this in the expression:
=Code.DisplayCnt()
That should do it.
--
Message posted via http://www.sqlmonster.com|||oops, the comment for ResetCnt should be:
' This function will reset the global count.
--
Message posted via http://www.sqlmonster.com|||> Not sure why you are doing this
I'm doing this because I've got a group that repeats its headers halfway
down the page. I just want the headers at the top of the page, so I'm using
the counter to trigger the visibility property on that particular header. If
the counter > 1 on a page, then hide. Else show. Then I reset the counter
at the page footer.
Your code worked perfectly! I just could figure it out to save my life. Of
course, I wasn't looking at using functions either.
Thank you so very very very much. You taught me something valuable today.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Patrick P via SQLMonster.com" wrote:
but... anyway.
> Put this code in your code section of report properties:
> Public Shared gintCnt As Integer ' This will hold the count.
> Public Shared Function IncDispCnt() As String
> ' This function will increment count and output result.
> gintCnt += 1
> Return gintCnt.ToString
> End Function
> Public Shared Function DisplayCnt() As String
> ' This function will output result.
> Return gintCnt.ToString
> End Function
> Public Shared Function IncCnt() As String
> ' This function will increment count.
> gintCnt += 1
> Return ""
> End Function
> Public Shared Function ResetCnt() As String
> ' This function will increment count.
> gintCnt = 0
> Return ""
> End Function
> I gave you a few options there. Add a textbox to the header you want to
> count and place this in the expression:
> =Code.IncCnt()
> In the page footer add a textbox and place this in the expression:
> =Code.DisplayCnt()
> That should do it.
> --
> Message posted via http://www.sqlmonster.com
>

counting based on bit flag

I have a table with an id field (int) and a bit flag. example below

id flag

1 true

1 true

1 false

1 true

2 true

2 false

I am looking for a query that will provide me the following results if possible

id true false

1 3 1

2 1 1

Any and all help is appreciated.

My efforts so far aren't worth sharing. I am looking for completely new approaches.

Thanks a ton

Use something like this:

Code Snippet

DECLARE @.MyTable table
( ID int,
Flag smallint
)

INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 1, 0 )
INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 2, 1 )
INSERT INTO @.MyTable VALUES ( 2, 0 )

SELECT
[ID],
True = sum( CASE Flag WHEN 1 THEN 1 ELSE 0 END ),
False = sum ( CASE Flag WHEN 0 THEN 1 ELSE 0 END )
FROM @.MyTable
GROUP BY [ID]

ID True False
-- -- --
1 3 1
2 1 1

|||Excellent!! Thank you so much!

counter of the group by statment

hi,

Lets say I have a table called test

create table test(

number int,

[str] nvarchar(50)

)

table content, for example:

8,'a'

8,'b'

8,'c'

5,'a'

5,'h'

I want to get a result with one more column that indicates on the asc counter

of the group by clause. i.e.

somthing like this:

number counter

8, 1

5, 2

and so on...

it does not realy matter regarding the query - All I want is to add one more column to the query that indicates on the count of the group by.so if i have 2 rows with number 8 and 2 rows with number 5 I would like to see in that column 1 (at the 8 number rows) and 2 (on the 5 number rows) and so on.. as the number is changing the counter is going up by 1.

Hi,

I think you might need to take a look at the RANK function: http://msdn2.microsoft.com/en-us/library/ms176102.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

You can use the dense_rank function to do this. The sample query assumes that you want to return all rows

create table #test(

number int,

[str] nvarchar(50)

)

insert into #test
select 8,'a'
union all
select 8,'b'
union all
select 8,'c'
union all
select 5,'a'
union all
select 5,'h'

select number,
dense_rank() over (order by number) as dense_rank
from #test

drop table #test
go

number dense_rank
-- --
5 1
5 1
8 2
8 2
8 2

|||

Hi,

Thank you for this info, but I need to use this rank in my where clause:

select number,

dense_rank() over (order by number) as dense_rank

from #test

where dense_rank = 1

but i get an error. How can I use it in the where clause?

thx