converting decimal to time

how do you convert a numeric to time format if it shows hours but a decimal figure for Minutes. For example if I have hours in decimal format like this

28.5000

but I want to show it in this format:

28:30:00

where 28 = hours, 30 = minutes, 00 = seconds

Thanks.This is a presentation issue. It should be handled at the client, not the server. Dealing with formatting inside the database is just a recipe for problems later.

-PatP|||What Pat said

DECLARE @.hours decimal(15,4)
SELECT @.hours = 28.5
SELECT RIGHT('00' + CONVERT(varchar(2),FLOOR(@.hours)),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60))),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60)-FLOOR(((@.hours-FLOOR(@.hours))*60)))*60),2)|||Displaying it as 28:30:00 is a presentation issue, but converting it to a valid datetime format falls within the scope of the database server:declare @.Hours decimal (6, 4)
set @.Hours = 28.5
select dateadd(minute, @.Hours * 60, 0)|||I was gonna give them that, but I realized it wasn't what they asked for...

Hours of what BTW...sounds like derived data gotta be careful with that

converting datetime int

I have tables with columns that stores datetime data in int format on
SQL server 2000. For example, the datetime for '4/5/2004
00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
1081180799. I need to generate reports that display datetime columns
in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
my query statment.

select iorg_name as org, ref_num as [ticketnum], c_first_name as
[firstname], c_last_name as [lastname], sym as type, [description] as
summary, status, dateadd(s,open_date,'12/31/1969 08:00:00pm') as
opened, dateadd(s,last_mod_dt,'12/31/1969 08:00:00pm') as irt,
dateadd(s,close_date,'12/31/1969 08:00:00pm') as closed from
AHD.dbo.HDreports reportview WHERE reportview.open_date >= 1080882000
AND reportview.open_date <= 1081227599.

The result shows correctly with those records that are in daylight
saving time. Those records in standard time show 1 hour behind.

Does anyone know how to make this query correctly display the data in
properly?js (androidsun@.yahoo.com) writes:
> I have tables with columns that stores datetime data in int format on
> SQL server 2000. For example, the datetime for '4/5/2004
> 00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
> 1081180799. I need to generate reports that display datetime columns
> in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
> my query statment.
>...
> The result shows correctly with those records that are in daylight
> saving time. Those records in standard time show 1 hour behind.
> Does anyone know how to make this query correctly display the data in
> properly?

That was a very odd way of storing dates, and probably not the best one.
Apparently this is some variation of Unix, where time is counted as number
of seconds since 1970-01-01 00:00:00, except that here the staring point
is 1969-12-30 20:00:00.

SQL Server is not timezone aware, so you should not expect to be able
to get fully accurate results. You are probably best of getting the
integer value to the client, and try the Windows functions for date
and time. They are likely to work out better.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Converting Access report to Reporting Services

Hi,

I am trying to convert an Access report where I use expressions to calculate the number of males/females for example.

I have used the following expression, but it always returns an incorrect figure:

=Sum(IIf(Fields!gender.Value ="Female", 1, 0))

When I check the query however it has 46 rows = to 'Female', but when I previes the report it always comes out as 24.

What's causing this?

Thanks

Hi,

From your description, it seems the expression doesn't return the right number of those 'Female' field, right?

What I can see is to use a TRIM function to remove all the leading and trailing while-spaces characters from the value in each filed. Sometimes theses leading and trailing white-spaces characters may cause the expression "IIf(Fields!gender.Value = "Female", 1, 0)" returns 0. You may use TRIM to have a try, see the following code:

=Sum(IIf(TRIM(Fields!gender.Value) = "Female", 1, 0))

Thanks.

|||

Thanks, that seems to have worked.
I wasn't aware of the leading and trailing white-spaces.

Converting a string to number

Good morning all, ok im trying to convert a string to a numerical value. The problem I am having is that the number has 2 dashes in it. Example and this is always the same 0500-010-95. Is there any way i can change this to a number value so i can create a grouping. The first set is the main the second set is sub-account and the third is department. I am using CR 8.5. I tried converting to a number using the ToNumber({mystringfield}) but it told me it was not a numerical value.

Thanks a lot.

Bryan
Bmcafee@.regio-tile.comTry
ToNumber(replace({mystringfield},'-',''))

Converting a Oracle PL/SQL command into MS SQL

hi

I was wondering if anyone would be able to advise on converting oracle PL/SQL features into MSSQL.

For example i have the following sequence + trigger below but cant find any information on creating a sequence in MSSQL, is it possible?

create sequence a_SEQ
start with 001
increment by 1

create or replace trigger a_TG
before insert a
for each row
begin
select (concat('D',(cast(a_SEQ.nextval as varchar(4))))) into :new.a_id from dual;
end;

any links or tutorials would be great, i've got a couple of MSSQL 2005 books which do explain triggers but examples are really needed to understand the full functionality.

cheersAs it was for SQL 2000, the SQL 2005 Books Online (BOL) is for me the quickest way to research something.

The Oracle squence is one of the things I miss in SQL Server. The thing that comes close is an IDENTITY column. In your example you seem to generate an unique number for a table. So define a_id as an IDENTITY column (see CREATE TABLE in the BOL) and forget about the trigger.

Note, after rereading this, the IDENTITY column works better/simpler than a sequence :)|||hi there

yes that identity column works great and only takes a second through the GUI.

Just with oracle we were taught to insert a letter before the unique ID to help identify the tables more, (this was done using a sequence + trigger).

for example on a table called detective instead of:
ID fname sname
1 bil fish
2 fred frog
3 dave dog

It would display:
ID fname sname
D1 bil fish
D2 fred frog
D3 dave dog

This would help identify that the ID was coming from the detective table.

Do you know a way of doing something along these lines with MS SQL.

cheers|||I would say don't do it.

You know what table it's in and what column it's in

Personally I would avoid surrogate keys|||OK

cheers for the advise|||Personally I would avoid surrogate keys
Sputter...choke...cough...
...but anyway, PROPER use of surrogate keys would not require adding prefixes to indicate their location. That should be discouraged. A surrogate key should have no inherent relationship to the data it identifies.

Converting

Hi, can you help me converting some queries from ASP application Oracle to
SQL SERVER 2000?
example:
1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
hh24:mi:ss')"
2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
How can it be converted to SQL Server?Hi
You can use the convert function to format dates as text or cast to cast
between datatypes. If your datestring is in a "safe" format e.g CCYYMMDD or
'CCYY-MM-DDTHH:MM:SS.NNN then you could let it do an implicit conversion e.g.
and p.DATACAD < '20071120'
If you specify a date with no time it will default to midnight 00:00:00.000
Datetime granularity is 3.33 milliseconds and smalldatetime is accurate to 1
minute.
You can try this out using Query Analyser or Management studio e.g.
SELECT CAST('11:31:08.000' AS datetime),
CAST('11:31:08.000' AS smalldatetime),
CAST('20071120' AS datetime),
CAST('20071120' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS datetime),
CAST('2007-11-20T23:59:59.990' AS datetime),
CAST('2007-11-20T23:59:59.992' AS datetime),
CAST('2007-11-20T23:59:59.993' AS datetime),
CAST('2007-11-20T23:59:59.994' AS datetime),
CAST('2007-11-20T23:59:59.996' AS datetime),
CAST('2007-11-20T23:59:59.997' AS datetime),
CAST('2007-11-20T23:59:59.998' AS datetime),
CAST('2007-11-20T23:59:59.999' AS datetime)
"Paulo" wrote:
> Hi, can you help me converting some queries from ASP application Oracle to
> SQL SERVER 2000?
> example:
> 1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
> hh24:mi:ss')"
> 2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
> How can it be converted to SQL Server?
>
>

Convert Punctuation to Spaces?

Hi,

I have a table of text. I need to search for whole words within this text...
For example, I need to be able to search for records that contain 'dog' but
not return 'hotdog' or 'dogma' for example.

I am doing this by throwing a space around both the records in the table and
the search word like this:
WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')

The problem is that punctuation needs to be stripped out of the text so that
it will still find "...walking the dog."

Is there a way to update, converting a certain set of characters into
another character (i.e. a space) and/or to do the same thing during the word
search query itself?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> Hi,
> I have a table of text. I need to search for whole words within this
text...
> For example, I need to be able to search for records that contain 'dog'
but
> not return 'hotdog' or 'dogma' for example.
> I am doing this by throwing a space around both the records in the table
and
> the search word like this:
> WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')
> The problem is that punctuation needs to be stripped out of the text so
that
> it will still find "...walking the dog."
> Is there a way to update, converting a certain set of characters into
> another character (i.e. a space) and/or to do the same thing during the
word
> search query itself?
> Thanks!

Assuming you have MSSQL 2000, you could write a UDF to remove all
punctuation characters from a string, but then you'd end up with this:

WHERE dbo.fn_RemovePunc(MyColumn) LIKE '% ' + @.SearchString + ' % '

That will probably cause a performance issue, because the UDF will be
invoked once per row during queries, although you could create a computed
column using the UDF and index it.

However, perhaps a better solution here would be to look at using full-text
indexing? The CONTAINS() predicate can do what you need, and is much more
powerful than LIKE.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:406e7002$1_1@.news.bluewin.ch...
> "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>> <CUT>For example, I need to be able to search for records that contain
'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
> The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

That helped tons. I got the basic "CONTAINS" predicate to work, but do not
get any results when I add "FORMSOF" into the mix. Do you see the problem
with the following?

WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
@.SearchIncludes) ')

All of the examples I found seemed to have a space and single quotes around
the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
the space or the single quotes.

Thanks!|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:dtjcc.16960$lt2.8344@.newsread1.news.pas.earth link.net...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:406e7002$1_1@.news.bluewin.ch...
> > "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> > news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> >> <CUT>For example, I need to be able to search for records that contain
> 'dog'
> >> but not return 'hotdog' or 'dogma' for example.
> >> <CUT>
> > The CONTAINS() predicate can do what you need, and is much more
> > powerful than LIKE.
> That helped tons. I got the basic "CONTAINS" predicate to work, but do not
> get any results when I add "FORMSOF" into the mix. Do you see the problem
> with the following?
> WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
> @.SearchIncludes) ')
> All of the examples I found seemed to have a space and single quotes
around
> the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
> the space or the single quotes.
> Thanks!

This may help:

http://oldlook.experts-exchange.com...Q_20711909.html

Fulltext is quite a specialized area, and it seems to have a number of
quirks, so you may want to consider posting questions in
microsoft.public.sqlserver.fulltext - you'll probably get a better response.

Simon|||> This may help:
>
http://oldlook.experts-exchange.com...Q_20711909.html
> Fulltext is quite a specialized area, and it seems to have a number of
> quirks, so you may want to consider posting questions in
> microsoft.public.sqlserver.fulltext - you'll probably get a better
response.

Thanks!|||>"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>><CUT>I need to be able to search for records that contain 'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
"Simon Hayes" <sql@.hayes.ch> replied in message
news:406e7002$1_1@.news.bluewin.ch...
>perhaps a better solution here would be to look at using full-text
> indexing? The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

Thanks Simon. The syntax needed is:

In SQL:
-- In the declarations or parameters:
@.Variable varchar(256) = 'FORMSOF(INFLECTIONAL,"word")'

-- Then, in the WHERE clause:
CONTAINS (TableName, @.Variable)

If passing the string from VB to a stored procedure, prepare the string in
VB with:
TheVariable= "'FORMSOF(INFLECTIONAL,""" & TheVariable & """)'"

Cheers!!

Convert numbers to Month Name

I have a stupid field in my database that doesn't hold a date but I have to use it to determine the month in my Chart in SSRS 2005. So for example, check this out:

http://www.webfound.net/chart_months.jpg

Ok, so how can I change those numbers to the Month Name? I tried MonthName() around my field in the expression builder, but that's only for a datetime field.

Write a UDF with input as int month and return it as varchar month. Use 13 CASE statements (12 for 12 months and 1 for error)|||

How about something like convert(datetime,'2006' + <fieldname> + '01') within your SQL statement?

for padding 0's

select convert(datetime,'2006' + fieldname + '01')
from
( select case <fieldname> when > 9 then <fieldname> else '0' + convert(char(1),<fieldname>) end fieldname ) derivedtable1

|||

The function MonthName, in SRSS2005, accepts an Integer parameter, so I'm not sure I understood the question.

|||hmm, then I wonder why that didn't work...MonthName(fieldname)|||

Paulo X , look at the link to my chart above. Those are integer values coming in from my dataset on a field in our DB table called systemmonth. Don't ask me why they did it that way but I need to take those values and convert them to Month Names....hopeing to do this either through SQL or preferably using a function in Reporting Server 2005 as you stated. I tried wrapping the systemmonth like this but it didn't have any affect:

MonthName(myfieldname)

|||

this is what I'm talking about, I put this in as a category group field and created this expression behind it:

=MonthName(Fields!SystemMonth.Value)

|||

What happens when you use MonthName? Is there any error message?
You say that you have used the MonthName function into the group expression. You must used it also in the Label expression! In fact, you may use MonthName only in the Label, you don't need to use it to group data.

Regards

|||

Hi,

Just try

=Monthname(1) or

=MonthName("1")

Is it working. then assign your field values. the second statement also works.

Amarnath

convert number to text

hi

can any one tell do we have any function to convert number to text.For example 25 to be converted to twenty five in SQLHello,

I'm working on it too... Still did not found a good solution though. Well, help would appreciated ! LOL

Thanks !|||Not sure why so many people ask the same question at once but see if my solution will help:

http://www.msdner.com/forum/thread586824.html

It could be expended if necessary...
Good Luck.

Convert number into Chinese Text

Please help to advoce how to convert a number into text (Chinese characters). For example, to display $2880.85 into:
貳仟捌佰捌拾元捌拾伍仙
Thankshow about making out a formular like

if = 2 then...?

other wise is quite hard... due to chinese have to enter the 佰捌, 元 and 仙...
thats difficult|||I can create a mapping but need to identify the correct location of the digit first. In this example, how do I separate the 2 from the entire figure so that it is equal to 2000 first, and the 1st 8 is 800, and so forth for the rest of the digits? What is the formula to do this identification?

If I can tick out the correct place of the digits, then I can map it to the correct translation, say if X's dec. place is in thousand, then translate 2 to Chinese text + the chinese thousand sign...

Pls help to define the formula to tick out the digits' dec. place...Thanks

Convert Nested Sets to Adjacency List

Hi,
Can anyone give me an example of how to convert a tree structure
stored as a nested set into an Adjacency List?
I.e
Table(Node_ID, Left, Right)
into
Table(Node_ID, Parent_ID)
I can't get my head around the sql to do it!
(im using SQLS 2000)
Thanks
Andrew
Here is one way to convert nested set to adjacency list:
CREATE TABLE NestedSet (
NodeId CHAR(1) NOT NULL PRIMARY KEY,
Lf INT NOT NULL,
Rg INT NOT NULL)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('A', 1, 9)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('B', 2, 3)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('C', 4, 7)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('D', 5, 6)
CREATE TABLE AdjacencyList (
NodeId Char(1) NOT NULL PRIMARY KEY,
ParentId Char(1) NULL)
INSERT INTO AdjacencyList
SELECT A.NodeId,
B.NodeId As ParentId
FROM NestedSet A
LEFT OUTER JOIN NestedSet B
ON B.Lf = (SELECT MAX(C.Lf)
FROM NestedSet C
WHERE C.Lf < A.Lf
AND C.Rg > A.Rg)
SELECT NodeId, ParentId FROM AdjacencyList
DROP TABLE NestedSet
DROP TABLE AdjacencyList
Regards,
Plamen Ratchev
http://www.SQLStudio.com

Convert Nested Sets to Adjacency List

Hi,
Can anyone give me an example of how to convert a tree structure
stored as a nested set into an Adjacency List?
I.e
Table(Node_ID, Left, Right)
into
Table(Node_ID, Parent_ID)
I can't get my head around the sql to do it!
(im using SQLS 2000)
Thanks
AndrewHere is one way to convert nested set to adjacency list:
CREATE TABLE NestedSet (
NodeId CHAR(1) NOT NULL PRIMARY KEY,
Lf INT NOT NULL,
Rg INT NOT NULL)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('A', 1, 9)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('B', 2, 3)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('C', 4, 7)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('D', 5, 6)
CREATE TABLE AdjacencyList (
NodeId Char(1) NOT NULL PRIMARY KEY,
ParentId Char(1) NULL)
INSERT INTO AdjacencyList
SELECT A.NodeId,
B.NodeId As ParentId
FROM NestedSet A
LEFT OUTER JOIN NestedSet B
ON B.Lf = (SELECT MAX(C.Lf)
FROM NestedSet C
WHERE C.Lf < A.Lf
AND C.Rg > A.Rg)
SELECT NodeId, ParentId FROM AdjacencyList
DROP TABLE NestedSet
DROP TABLE AdjacencyList
Regards,
Plamen Ratchev
http://www.SQLStudio.com

Convert Nested Sets to Adjacency List

Hi,
Can anyone give me an example of how to convert a tree structure
stored as a nested set into an Adjacency List?
I.e
Table(Node_ID, Left, Right)
into
Table(Node_ID, Parent_ID)
I can't get my head around the sql to do it!
(im using SQLS 2000)
Thanks
AndrewHere is one way to convert nested set to adjacency list:
CREATE TABLE NestedSet (
NodeId CHAR(1) NOT NULL PRIMARY KEY,
Lf INT NOT NULL,
Rg INT NOT NULL)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('A', 1, 9)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('B', 2, 3)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('C', 4, 7)
INSERT INTO NestedSet (NodeId, Lf, Rg) VALUES ('D', 5, 6)
CREATE TABLE AdjacencyList (
NodeId Char(1) NOT NULL PRIMARY KEY,
ParentId Char(1) NULL)
INSERT INTO AdjacencyList
SELECT A.NodeId,
B.NodeId As ParentId
FROM NestedSet A
LEFT OUTER JOIN NestedSet B
ON B.Lf = (SELECT MAX(C.Lf)
FROM NestedSet C
WHERE C.Lf < A.Lf
AND C.Rg > A.Rg)
SELECT NodeId, ParentId FROM AdjacencyList
DROP TABLE NestedSet
DROP TABLE AdjacencyList
Regards,
Plamen Ratchev
http://www.SQLStudio.com

convert money datatype

Hello!

I need to convert money datatype into string without decimal points.

For example, I have column pmt_amt (money datatype)=27.00.

Is there a way to convert this amount into format "2700" without decimals?

Thanks,

Lena

Can you do that in the presentation layer?

declare @.m money

set @.m = 27

select @.m, str(@.m * 10000, 15, 0)

set @.m = 27.0015

select @.m, str(@.m * 10000, 15, 0)

AMB

|||

Thank you!

I tried your conversion and here's my results:

value in table converted

47.02 470200 1202.78 12027800 411.91 4119100 20401.78 204017800 711.01 7110100 517.83 5178300 11756 117560000 773 7730000 3439.75 34397500 4062.66 40626600

How can I get rid of zeroes in the end of converted values?

E. G.: for 40626600 I need it t be 406266.

|||

I would 'refine' AMB's suggestion with the following:

If you want the results to have ONLY 2 numbers to the right of the decimal, then use 100 instead of 10000 in the str() function. That is because the money datatype has four (4) numbers to the right of the decimal, and EVEN though you often ignore them, they are there.

(It will still round up/down as needed.)

And if you have really large numbers, the '15' in the string function may need to be increased.

|||

Money data type has 4 decimal digits. If you want to consider just the first two, then multiply the value by 100 instead 10000.

declare @.m money

set @.m = 4062.66

select @.m, str(@.m * 100, 15, 0)

AMB

|||

Thank you for all your replies.

It helped a lot.

convert minutes to hours

hi,

I want to convert minutes to hours. for example field_minutes=130minutes to 2:10 hours...

select field_minutes from table> how can I do?

Not sure what this has got to do with SSIS but its an interesting little puzzle for a Friday afternoon anyway.

Paste the following into SSMS/QA and run it:

declare @.mins int

set @.mins = 130

select cast(@.mins/60 as varchar(5)) + ':' + RIGHT('0' + cast(@.mins%60 as varchar(2)), 2)

There are probably other, better, ways!!!

-Jamie

convert millisecond to "hh:mm:ss" format

Hello guys,

I have a column of integer data type that contains a millisecond data(for example 54013). I want to convert this value to the corresponding "hh:mm:ss" format. Can anybody help me with this issue?

Sincerely,

amde

How about this

declare @.SomeMilliSecondsNumber bigint
select @.SomeMilliSecondsNumber =54013

select convert(varchar,dateadd(ms,@.SomeMilliSecondsNumber,0),114)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks a lot Denis!

Amde

|||

Glad I could help

Denis the SQL Menace

http://sqlservercode.blogspot.com/

convert millisecond to "hh:mm:ss" format

Hello guys,

I have a column of integer data type that contains a millisecond data(for example 54013). I want to convert this value to the corresponding "hh:mm:ss" format. Can anybody help me with this issue?

Sincerely,

amde

How about this

declare @.SomeMilliSecondsNumber bigint
select @.SomeMilliSecondsNumber =54013

select convert(varchar,dateadd(ms,@.SomeMilliSecondsNumber,0),114)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks a lot Denis!

Amde

|||

Glad I could help

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Convert Informix Stored Procedure

I would like to convert a couple informix stored procedures to SQL
Server stored procedures. I have no idea how to accomplish this. Here
is an example of one of the procedures I need to convert.

drop function mnaf_calc_calendar_quarter;

CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
--************************************************** ***************************
-- Name: mnaf_calc_calendar_quarter
-- Description/Notes:
-- Calculates the most recent calendar quarter based on the end date.
--
-- Parms:
-- End Date.
-- Returns:
-- The calculated period start date and end date.
--
--************************************************** ***************************
-- Revisions:
-- PgmrDate # Description
-- HPI05/03/2005
--************************************************** ***************************

RETURNING date, date;

DEFINE dtStartDate date;
DEFINE dtEndDate date;

LET dtStartDate = mdy(12,31,1899);
LET dtEndDate = pEndDate;

-- If the end date parameter is equal to a calendar quarter,
-- calculate the start date by subtracting three months.
IF month(pEndDate) = 3 or month(pEndDate) = 6 or
month(pEndDate) = 9 or month(pEndDate) = 12 then

LET dtEndDate = pEndDate;

ELSE

-- Otherwise find the closest previous calendar quarter end date
-- then calculate the start date.
IF month(pEndDate) = 1 or month(pEndDate) = 4 or
month(pEndDate) = 7 or month(pEndDate) = 10 then

-- Subtract 1 month off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
month);

ELSE

-- Month must be equal to 2, 5, 8, 11
-- Subtract 2 months off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
month);

END IF;

END IF;

-- Calcuate the start date by subtracting off two months
LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);

RETURN dtStartDate, dtEndDate;

END FUNCTION;

grant execute on mnaf_calc_calendar_quarter to public;"Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1118858793.011839.15120@.g49g2000cwa.googlegro ups.com...
>I would like to convert a couple informix stored procedures to SQL
> Server stored procedures. I have no idea how to accomplish this. Here
> is an example of one of the procedures I need to convert.
> drop function mnaf_calc_calendar_quarter;
> CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
> --************************************************** ***************************
> -- Name: mnaf_calc_calendar_quarter
> -- Description/Notes:
> -- Calculates the most recent calendar quarter based on the end date.
> --
> -- Parms:
> -- End Date.
> -- Returns:
> -- The calculated period start date and end date.
> --
> --************************************************** ***************************
> -- Revisions:
> -- Pgmr Date # Description
> -- HPI 05/03/2005
> --************************************************** ***************************
> RETURNING date, date;
> DEFINE dtStartDate date;
> DEFINE dtEndDate date;
> LET dtStartDate = mdy(12,31,1899);
> LET dtEndDate = pEndDate;
> -- If the end date parameter is equal to a calendar quarter,
> -- calculate the start date by subtracting three months.
> IF month(pEndDate) = 3 or month(pEndDate) = 6 or
> month(pEndDate) = 9 or month(pEndDate) = 12 then
> LET dtEndDate = pEndDate;
> ELSE
> -- Otherwise find the closest previous calendar quarter end date
> -- then calculate the start date.
> IF month(pEndDate) = 1 or month(pEndDate) = 4 or
> month(pEndDate) = 7 or month(pEndDate) = 10 then
> -- Subtract 1 month off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
> month);
> ELSE
> -- Month must be equal to 2, 5, 8, 11
> -- Subtract 2 months off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
> month);
> END IF;
> END IF;
> -- Calcuate the start date by subtracting off two months
> LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);
> RETURN dtStartDate, dtEndDate;
> END FUNCTION;
> grant execute on mnaf_calc_calendar_quarter to public;

See "Date and Time Functions" in Books Online - DATEPART() and DATEADD()
will probably be the ones you're looking for. This article might also be
useful for general background information about manipulating datetime data:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon

convert dynamically generated parameters list into stored proc

I have the following ASP code that builds part of the example SQL statement below (it's the same SQL as in my earlier thread here (http://www.dbforums.com/showthread.php?t=1214044) but a very different question):

if sFindTicketEventId > 0 then sSQL = sSQL & " AND [tblEvents].[id]=" & sFindTicketEventId
if sFindTicketStandId > 0 then sSQL = sSQL & " AND [tblStands].[id]=" & sFindTicketStandId

SELECT
[tblC].[id] AS CombinationID,
[tblC].[availability],
[tblC].[description],
[tblC].[price] AS combinationPrice,
[tblC].[combination_open],
[tblT].[TicketID] AS TicketID,
[tblT].[price] AS ticketPrice,
[tblT].[availability],
[tblT].[ticket_open],
[tblT].[quantity],
[tblT].[event_name],
[tblT].[event_open],
[tblT].[stand_name],
[tblT].[stand_open],
[tblT].[admission_start_date],
[tblT].[admission_end_date],
[tblT].[date_open],
[tblT].,
[tblT].,
[tblT2].[description],
[tblT2].[admin_description]
FROM(
SELECT
[tblCombinations].[id],
[tblTickets].[id] As TicketID, [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
[tblCombinations_Tickets].[quantity],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date]
FROM [tblCombinations]
LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id]
LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id]
LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS [tblBookingMinQuantities] ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS [tblBookingMaxQuantities] ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
WHERE 1=1
[B]AND [tblEvents].[id]=2
[B]AND [tblStands].[id]=3
--AND [tblAdmissionDates].[id]=@.admissionDateId
--AND [tblBookingDates].[id]=@.bookingDateId
--AND [tblTicketConcessions].[id]=@.concessionId
--AND [tblBookingMinQuantities].[id]=@.bookingMinQuantityId
--AND [tblBookingMaxQuantities].[id]=@.bookingMaxQuantityId
--AND [tblMemberships].[id]=@.membershipId
GROUP BY
[tblCombinations].[id],
[tblTickets].[id],
[tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
[tblCombinations_Tickets].[quantity],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date]
) as [tblT]
JOIN [tblCombinations] as [tblC] on [tblT].[id]=[tblC].[id]
LEFT JOIN [tblTickets] as [tblT2] on [tblT].[TicketID]=[tblT2].[id]

I want to turn this SQL into a stored proc; there are currently about 8 parameters that I want to pass into it. The field value for each will be either NULL or a positive integer, and the paramater will be passed in as an integer.

If the passed parameter value is a positive integer then it should return all records where the corresponding field value matches that integer. If the passed parameter is 0, it should return all rows regardless of whether the field value is an integer or NULL.

And I can't for the life of me figure out how to do it. Do I need an IF statement in there or something?

:confused:Hi
A common method is:
WHERE (MyField = @.MyParam OR @.MyParam = 0)
I read an article somewhere though that poohed poohed this as the optimiser can't use the index or something though.|||Well, seeing as my state of blissful ignorance safely censored your "optimiser" comment, I can happily report that the solution works great :) Thanks.

Convert digits to letters

I want to know if somebody have a fucntion to convert numbers to letters.
for Example: 56 to Fifty Six or
10,599 to Ten Thousand five hundred and ninety nine.
Please help me...
Radhamescheck this link
[url]http://www.novicksoftware.com/UDFofW/Vol2/T-SQL-UDF-Vol-2-Num-9-udf_Num_ToWords
.htm[/url]
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Radhames" wrote:

> I want to know if somebody have a fucntion to convert numbers to letters.
> for Example: 56 to Fifty Six or
> 10,599 to Ten Thousand five hundred and ninety nine.
> Please help me...
> Radhames|||Thanks omnibuzz
I going to implement this function in my database to convert numbers
to Spanish Words.
:)
"Omnibuzz" wrote:
> check this link
> [url]http://www.novicksoftware.com/UDFofW/Vol2/T-SQL-UDF-Vol-2-Num-9-udf_Num_ToWor
ds.htm[/url]
> --
> -Omnibuzz
> --
> Please post ddls and sample data for your queries and close the thread if
> you got the answer for your question.
>
> "Radhames" wrote:
>|||Don't forget to select the fact that he answered your question. Its nice to
see the little green check mark next to the question once it has been
answered.
:)
"Radhames" wrote:
> Thanks omnibuzz
> I going to implement this function in my database to convert numbers
> to Spanish Words.
> :)
> "Omnibuzz" wrote:
>|||> Don't forget to select the fact that he answered your question. Its nice
> to
> see the little green check mark next to the question once it has been
> answered.
What little green check mark?
Please keep in mind we're not all using a frilly web gui.|||A common function in report writers converts numbers into words so that
they can be used to print checks, legal documents and other reports.
This is not a common function in SQL products, nor is it part of the
standards.
A method for converting numbers into words using only standard SQL by
Stu Bloom follows. This was posted on 2002 Jan 02 on the SQL Server
Programming newsgroup.
First, create a table
CREATE TABLE NbrWords
(number INTEGER PRIMARY KEY,
word VARCHAR(30) NOT NULL);
Then populate it with the literal strings of all NbrWords from 0 to
999. Assuming that your range is 1 - 999,999,999 use the following
query; it should be obvious how to extend it for larger numbers and
fractional parts.
CASE WHEN :num < 1000
THEN (SELECT word FROM NbrWords
WHERE number = :num)
WHEN :num < 1000000
THEN (SELECT word FROM NbrWords
WHERE number = :num / 1000)
|| ' thousand '
|| (SELECT word FROM NbrWords
WHERE MOD (number = :num, 1000))
WHEN :num < 1000000000
THEN (SELECT word FROM NbrWords
WHERE number = :num / 1000000)
|| ' million '
|| (SELECT word FROM NbrWords
WHERE number = MOD((:num / 1000), 1000))
|| CASE WHEN MOD((:num / 1000), 1000) > 0
THEN ' thousand '
ELSE '' END
|| (SELECT word FROM NbrWords
WHERE number = MOD(:num, 1000))
END;