Showing posts with label converts. Show all posts
Showing posts with label converts. Show all posts

Thursday, March 22, 2012

Converting a number to hexadcimal

Is there any function in SQL-Server which converts a number into its Hexadecimal equivalent..? if not, how should I convert a number to its hexadecimal equivalent..?
Thanks
Jakesomething like:
SELECT CAST( 123456 AS BINARY(4) )|||Originally posted by harshal_in
something like:
SELECT CAST( 123456 AS BINARY(4) )

Thanks Harsh

Sunday, March 11, 2012

Convert Time

Hi,

Hopefully this is an easy one. I need a function to call from my page footer that takes the NOW date/time value and converts it from Eastern Standard Time to Mountain Standard Time. (My server is on EST time and my users are all on MST time) Of course the function needs to handle daylight savings as well.

thanks!

Why don't you just subtract two hours from the NOW date/time value to get MST.....

Regardless of where your server is, you should be able to convert the time value to any timezone in the world.

|||

That would work until daylight savings...then it's 3 hours difference. I was really hoping to get a custom function from someone...Thx

|||

So you're saying that NOW doesn't adjust for daylight savings?

|||

I'm saying the following:

If NOW is 2pm EST then MST is 12PM ( subtracting 2 hours from EST will work)

When daylight savings kicks in then NOW is 3pm EST and MST is still 12pm (subtracting 2 hours from EST give me 1pm which is incorrect.)

When I say MST I mean arizona time which does not change for daylight savings.

Hope that makes sense..

|||

Yes that makes sense. After a brief research of the forums and google, I've turned up empty handed. Hopefully someone else knows how to do it.

I am assuming that you can't simply change your server's time to MST.

|||

R.K.S. wrote:

I'm saying the following:

If NOW is 2pm EST then MST is 12PM ( subtracting 2 hours from EST will work)

When daylight savings kicks in then NOW is 3pm EST and MST is still 12pm (subtracting 2 hours from EST give me 1pm which is incorrect.)

When I say MST I mean arizona time which does not change for daylight savings.

Hope that makes sense..

You are not looking for DateTime but TimeZone there are only work around solutions but coming in VS2008 try the thread below for your options.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

Thanks Caddre, Not the answer i was hoping for but at least i know i wasn't overlooking something simple.

What about this: I already have a function written in C# that will do the conversion. Can't i call a custom function like this in a page footer? Can the custom function exist in an assembly or in the custom code window...

Thx

|||

The Report properties includes code box what I don't know is if you can run it in a footer, you could also check the ReportViewer control it may have a way to use .NET code much easier than normal reports.

|||

If anyone is interested you can use this function to convert to whatever local time your users are on (assuming all your users are on a single timezone) So below, my server is EST and my users are all on MST

Public Shared Function AdjustDateTime(ByVal dt as DateTime)

Dim tz as System.TimeZone
tz = System.TimeZone.CurrentTimeZone

Dim utc as System.DateTime
utc = tz.ToUniversalTime(dt)

'Convert EST to MST
if Not tz.IsDaylightSavingTime(dt) then
AdjustDateTime =utc.ToLocalTime().AddHours(-2)
else
AdjustDateTime =utc.ToLocalTime().AddHours(-3)
end if

End Function

Convert Time

Hi,

Hopefully this is an easy one. I need a function to call from my page footer that takes the NOW date/time value and converts it from Eastern Standard Time to Mountain Standard Time. (My server is on EST time and my users are all on MST time) Of course the function needs to handle daylight savings as well.

thanks!

Why don't you just subtract two hours from the NOW date/time value to get MST.....

Regardless of where your server is, you should be able to convert the time value to any timezone in the world.

|||

That would work until daylight savings...then it's 3 hours difference. I was really hoping to get a custom function from someone...Thx

|||

So you're saying that NOW doesn't adjust for daylight savings?

|||

I'm saying the following:

If NOW is 2pm EST then MST is 12PM ( subtracting 2 hours from EST will work)

When daylight savings kicks in then NOW is 3pm EST and MST is still 12pm (subtracting 2 hours from EST give me 1pm which is incorrect.)

When I say MST I mean arizona time which does not change for daylight savings.

Hope that makes sense..

|||

Yes that makes sense. After a brief research of the forums and google, I've turned up empty handed. Hopefully someone else knows how to do it.

I am assuming that you can't simply change your server's time to MST.

|||

R.K.S. wrote:

I'm saying the following:

If NOW is 2pm EST then MST is 12PM ( subtracting 2 hours from EST will work)

When daylight savings kicks in then NOW is 3pm EST and MST is still 12pm (subtracting 2 hours from EST give me 1pm which is incorrect.)

When I say MST I mean arizona time which does not change for daylight savings.

Hope that makes sense..

You are not looking for DateTime but TimeZone there are only work around solutions but coming in VS2008 try the thread below for your options.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

Thanks Caddre, Not the answer i was hoping for but at least i know i wasn't overlooking something simple.

What about this: I already have a function written in C# that will do the conversion. Can't i call a custom function like this in a page footer? Can the custom function exist in an assembly or in the custom code window...

Thx

|||

The Report properties includes code box what I don't know is if you can run it in a footer, you could also check the ReportViewer control it may have a way to use .NET code much easier than normal reports.

|||

If anyone is interested you can use this function to convert to whatever local time your users are on (assuming all your users are on a single timezone) So below, my server is EST and my users are all on MST

Public Shared Function AdjustDateTime(ByVal dt as DateTime)

Dim tz as System.TimeZone
tz = System.TimeZone.CurrentTimeZone

Dim utc as System.DateTime
utc = tz.ToUniversalTime(dt)

'Convert EST to MST
if Not tz.IsDaylightSavingTime(dt) then
AdjustDateTime =utc.ToLocalTime().AddHours(-2)
else
AdjustDateTime =utc.ToLocalTime().AddHours(-3)
end if

End Function

Thursday, March 8, 2012

Convert SQL between Oracle and SQL Server

Hi, Is there a tool that anyone here is aware of which converts SQL, stored procedures and such stuff from Oracle compatibility to SQL Server compatibility? I have some Oracle based SQL that I wud like to convert into T-sql instead of re writing the whole T-SQL..
Thanks,This is one of those "holy grail" kinds of quests. I don't know of such a beast, although there are many vendors that are trying to sell products that get part of the way to solving this problem.

-PatP|||Thanks Pat. Can you give me an expample of such vendor so that I can try their evaluation version?|||Try searching for SwisSQL - under download.com.
http://www.eweek.com/article2/0,3959,990163,00.asp for information.|||I can't really give you any good suggestions for automated conversion between SQL dialects. While I've looked at a number of them in the past, I haven't looked in at least two years. There isn't anything that does what I consider a tolerable job for DML.

Now DDL (schema) conversion is another story. Assuming that the source database uses DRI (declarative referential integrity), then there are products that do a good job of moving your table structure from one database platform to another.

-PatP|||Thanks Satya, That is exactly what I am looking for. I will give it a try to see if it can convert my scripts.|||You fail to mention what versions...

Also DDL converts easier bexcuase it is waaaaaaaaaaaaay more straight forward...

Also the level of complexity of the DML could make a "translator" reach potentially incorrect results...

How do you plan to audit the conversion?

You will need to makes sure the results produced are identical...

Also, Oracle uses A LOT od cursors, because of it's architecture...

That would not be a good think for sql server

Why are you converting?|||I already have the DDL ready which I coverted and cleaned manually. I have some data integrity check and other migration scripts which are in Oracle that I need to make them compatible and equavalent to SQL Server 2000. Yes, we do have a lot of cursors in Oracle. How do we handle them then?
Thanks.|||Are they reference cursors?

Can you supply a sample?

What's the front end written in? Or is it all batch?

Do you have a small sample package?

Maybe you can attach one.|||This scripts verify the ingegrity of data in our database...
We have functions, procedures, PL/SQL that we cant to convert..
Here are some sample blocks..It is all a batch sql run against our DB which inturn writes all the errors/inconsistancies into an table called error_report.
----
create or replace function getlatestrelchg(itemid number) return number is
cursor csr is select a.change from rev a, change b where a.item = itemid
and (b.class = 6000 or b.class = 1450) and (b.statustype =3 or b.statustype=4)
and a.released = 1 and (b.delete_flag is null or b.delete_flag = 0) and a.change = b.id order by a.release_date desc;

relchgid number;
begin
open csr;
loop
fetch csr into relchgid;
exit;
end loop;
close csr;
return relchgid;

end;
/
--------
declare cursor nodecsr is select parentid from nodetable where parentid in
(select id from nodetable where objtype = 13 and id not in (11610, 16633, 16445)) group by parentid having count(*) != 4;
subclassId number;
Subclassdesc varchar(30);
begin
open nodecsr;
loop
fetch nodecsr into subclassId;
if (nodecsr%notfound) then
exit;
end if;
select description into Subclassdesc from nodetable where id = subclassId;
insert into error_report values ('NODETABLE', subclassId, 'Corrupted Subclass: ' || Subclassdesc);
commit;
end loop;
close nodecsr;
end;
/
-----------
rem This script ignores orphaned privilege masks (objtype=22)
rem
declare
cursor csr is select id, parentid, objtype, inherit from nodetable;
nodeID number;
parID number;
inheritID number;
errorCount number;
objectType number;
tmp number;
tmp1 number;
begin
errorCount := 0;
open csr;
loop
if errorCount > 30 then
insert into error_report values ('NODETABLE', 0, 'Too many errors');
exit;
end if;
fetch csr into nodeID, parID, objectType, inheritID;
if csr%notfound then
exit;
end if;
if (objectType !=22 and parID != 0) then
select count(1) into tmp from nodetable where id = parID;
if tmp = 0 then
errorCount := errorCount + 1;
insert into error_report values ('NODETABLE', nodeID, 'parent node does not exist');
end if;
end if;
if (objectType !=107 and objectType != 9 and inheritID != 0) then
select count(1) into tmp from nodetable where id = inheritID;
if (tmp=0) then
errorCount := errorCount + 1;
insert into error_report values ('NODETABLE', nodeID, 'inherit node does not exist');
end if;
end if;
-- check the comma in the attribute name
select count(1) into tmp1 from nodetable where description like '%,%'and id = nodeid and objtype = 1 and parentid !=12863;
if tmp1>0 then
errorCount := errorCount + 1;
insert into error_report values ('NODETABLE', nodeID, 'attribute name contains bad character - comma');
end if;
end loop;
close csr;
end;
/
----------------|||I think a couple of SQL Server DBA heads just exploded...

Damn now I gotta clean my screen...

How much code do you have?

You're in for a pretty good re-write...

Again, why the migration?|||I am new to the T-SQL area and given the responsibility of replicating whatever we have in Oracle as we started supporting SQL Server for our product.|||Well, your 1st function doesn't seem to make much sense, I mean, you've got a cursor, then returns a single values?

Anyway, this is how you would convert it...there are other ways to write it btw...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getlatestrelchg]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getlatestrelchg]
GO

CREATE FUNCTION getlatestrelchg(@.emid int)
RETURNS int
AS
BEGIN
DECLARE @.relchgid int
SELECT @.relchgid = a.change
FROM rev a
INNER JOIN change b
ON a.item = itemid
WHERE ( b.class = 6000 or b.class = 1450)
AND ( b.statustype =3 or b.statustype=4)
AND a.released = 1
AND ( b.delete_flag is null or b.delete_flag = 0)
AND a.change = b.id
ORDER BY a.release_date desc

RETURN @.relchgid
END
GO|||Don't scare him/her with functions, Brett, I think he/she has a lot to catch up ;)|||I will be bugging you guys often from now on.|||I just converted the one they posted...

Hey, and you know what...a heavy dose of reality would be good here...I know both platforms...

It's going to be a major shift...

btw what's the product?|||Its a supply- chain product and there is a lot of dependency between the tables. So, the data integrity check is needed and heavily used.

Wednesday, March 7, 2012

Convert smalldatetime to mmm yy format in query

I am trying to write a simple query that retrieves the data field from a table (stored in the smalldatetime format) and converts the date to mmm yy format. The closest I can get is retrieving the date in the dd mmm yy format using the query below.

select convert(varchar(10),DATA_DATE,06) As DATA_DATE

If there an easy way to parse out the information I want? I also attempted to use the SUBSTR functions, but they always returned error messages.So you want to exclude the dd out of the datetime ?|||Try the following:

select right(convert(varchar(20),getdate(),06), len(convert(varchar(20),getdate(),06)) -3)|||That is exactly what I have been attempting to do. Thank you for your help.

Friday, February 24, 2012

Convert Long To DateTime

Hi.

I need to convert a vb function into a user-defined function in a stored procedure.
The vb function converts a long datatype into Date datatype.

Here is the VB function:

Function LongToTime(lTime As Long) As Date

LongToTime = (lTime \ 10000) / 24 + ((lTime Mod 10000) \ 100) / 1440 + (lTime Mod 100) / 86400

End Function

The function is used to convert a timestamp(hhmmss) into a more readable format.
Would it be possible to create a function similar to this in an SQL stored procedure?

Thanks in advance
Wesley

translation of your vb function should be

CREATE FUNCTION fn_LongToTime (@.lTime int)

RETURNS smalldatetime

AS

BEGIN

DECLARE @.dt smalldatetime

Select @.dt = convert(smalldatetime, floor(@.ts / 10000) / 24 + Floor((@.ts % 10000) / 100) / 1440) + (@.ts % 100) / 86400)

RETURN @.Kg

END

GO

You have to verify smalldatetime conversion is right...

Convert Long to Datetime

Hi.

I need to convert a vb function into a user-defined function in a stored procedure.
The vb function converts a long datatype into Date datatype.

Here is the VB function:

Function LongToTime(lTime As Long) As Date
LongToTime = (lTime \ 10000) / 24 + ((lTime Mod 10000) \ 100) / 1440 + (lTime Mod 100) / 86400
End Function

The function is used to convert a timestamp(hhmmss) into a more readable format.
Would it be possible to create a function similar to this in SQL?

Thanks
Wes

hi

there is no mod function in sql server

you have to use %

i think you can go ahead from here

regards

% (Modulo)

Provides the remainder of one number divided by another.

Syntax

dividend % divisor

Arguments

dividend

Is the numeric expression to divide. dividend must be any valid Microsoft? SQL Server? expression of the integer data type category. (A modulo is the integer that remains after two integers are divided.)

divisor

Is the numeric expression to divide the dividend by. divisor must be any valid SQL Server expression of any of the data types of the integer data type category.

Result Types

int

Remarks

The modulo arithmetic operator can be used in the select list of the SELECT statement with any combination of column names, numeric constants, or any valid expression of the integer data type category.

Examples

This example returns the book title number and any modulo (remainder) of dividing the price (converted to an integer value) of each book into the total yearly sales (ytd_sales * price).

USE pubs

GO

SELECT title_id,

CAST((ytd_sales * price) AS int) % CAST(price AS int) AS Modulo

FROM titles

WHERE price IS NOT NULL and type = 'trad_cook'

ORDER BY title_id

GO

|||declare @.lTime as int

select @.lTime = 153459

select convert(datetime, stuff(stuff(convert(varchar(6), @.lTime), 3, 0, ':'), 6, 0, ':'))

result : 1900-01-01 15:34:59.000

Friday, February 10, 2012

Convert char(24) to smalldatetime

I have a table that has a field with sample time that is set up as char(24).
I would like to write a SELECT statement converts char(24) to smalldatetime
format.
Table A
Counterdatetime char(24)
Please help me resolve this issue.
Thank You,What is the format of the data in that Counterdatetime column? There are
dozens of possible representations of a smalldatetime that fit in a char(24)
column...
Jacco Schalkwijk
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:67FB2AE9-F312-4274-B954-265098E50177@.microsoft.com...
> I have a table that has a field with sample time that is set up as
> char(24).
> I would like to write a SELECT statement converts char(24) to
> smalldatetime
> format.
> Table A
> Counterdatetime char(24)
> Please help me resolve this issue.
> Thank You,