Thursday, March 22, 2012
Converting a number to hexadcimal
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
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
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
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
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 pubsGO
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 intselect @.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 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,