Till New Year

SELECT 'Til the New Year ' || to_char(extract(YEAR FROM SYSDATE) + 1) || ' - ' ||
trunc(months_between(to_date('01.01.' || to_char(extract(YEAR FROM SYSDATE) + 1), 'dd.mm.yyyy'), SYSDATE)) ||
' months ' || to_char(LAST_DAY(SYSDATE) - SYSDATE) || ' day ' ||
to_char(23 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 1, 2))) || ' hh ' ||
to_char(59 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 4, 2))) || ' mi ' ||
to_char(59 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 7, 2))) || ' ss ' || '[today:' ||
to_char(SYSDATE, 'mm:dd:yyyy hh24:mi:ss') || ']' AS "Happy New Year!!!"
FROM dual;

Convert seconds to Day hour : min : sec


variable vSec number;
exec :vSec := 68400;
select to_char(trunc(:vSec/60/60/24),'09')||' [day]' ||to_char(trunc(MOD(:vSec/60/60,24)),'09') ||' [hh24]'||to_char(trunc(mod(:vSec,3600)/60),'09')||' [mi]'||to_char(mod(mod(:vSec,3600),60),'09')||' [ss]' from dual;


Result in one Line

with t as (
select 'string1' str from dual union all
select 'string2' str from dual union all
select 'string3' str from dual union all
select 'string4' str from dual union all
select 'string5' str from dual union all
SELECT 'string6' str from dual
)
select '~'||strs||'~' AS oneline from
( select ltrim(sys_connect_by_path(str, '~'), '~') as strs
from
( select str, lag(str) over (order by str) as prev_str
from t
)
start with prev_str is null
connect by prev_str = prior str
order by 1 desc
)
where rownum = 1;

Result set
SQL>
oneline
--------------------------------------------------
~string1~string2~string3~string4~string5~string6~


using

...
where
Instr('~string1~string2~string3~string4~string5~string6~', '~'||trim(rtrim(t.city))||'~') <> 0
...

Index info oracle

SELECT a.index_name, b.column_name, a.index_type, a.table_name
FROM USER_INDEXES a, USER_IND_COLUMNS b
WHERE a.index_name = b.index_name
AND a.table_name = upper('tDocument');


Dynamic SQL statement or an anonymous PL/SQL block

Some programs must build and process SQL statements where some information is not known in advance. A reporting application might build different SELECT statements for the various reports it generates, substituting new table and column names and ordering or grouping by different columns. Database management applications might issue statements such as CREATE, DROP, and GRANT that cannot be coded directly in a PL/SQL program. These statements are called dynamic SQL statements.

Dynamic SQL statements built as character strings built at run time. The strings contain the text of a SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. Placeholder names are prefixed by a colon, and the names themselves do not matter. For example, PL/SQL makes no distinction between the following strings:
'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'
To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

The following PL/SQL block contains several examples of dynamic SQL:

FireFox – We want to set a Guinness World Record

update

Microsoft SQL Server 2005 Courses

Live & Learn

How do I use GETDATE() within a User-Defined Function (UDF)?

SQL Server 2000 added the support for user-defined functions, but there are a few limitations which can be roadblocks at first. One is that you cannot use a non-deterministic function within a UDF, e.g. GETDATE(). So, let’s say you are trying to create a function that returns this moment, but a day in the future (e.g. tomorrow at this exact time). You would think about it this way:
CREATE FUNCTION dbo.addDay()
RETURNS DATETIME
AS
BEGIN
DECLARE @dt DATETIME
SET @dt = DATEADD(DAY, 1, GETDATE())
RETURN @dt
END

But you will get this error message:
Server: Msg 443, Level 16, State 1, Procedure addDay, Line 6
Invalid use of 'getdate' within a function.

Remove HTML Tags From a String

A lot of websites allow users to input text and submit it to the site.
This could be forums, blogs, content management systems etc.
Imaging if the user writes HTML into these form fields?
It could be perfectly harmless when used for styling, but it could also be used the wrong way.
A typical scenario would be when a user enters JavaScript that does harmful things or embedding a style sheet that ruins the websites layout.
This is normally referred to as Cross-Site Scripting (XSS).
We have to mitigate that risk, and that’s when regular expression comes to the rescue.
Here is a very simple method that strips all HTML tags from a string or just the harmful tags – you decide.

Global Variable 2nd method

App_Code\GData.cs
using System;
using System.Data;
...
public class UserInfo
{
private static string pin;
public static string Pin
{
get { return pin; }
set { pin = value; }
}
private static string fio;
public static string Fio
{
get { return fio; }
set { fio = value; }
}
}
public class QData
{
...
public class GData
{
public static void SetUserInfoPIN(string pin)
{
UserInfo.Pin = pin;
}
public static string GetUserInfoPIN()
{
return UserInfo.Pin;
}
...
}