convert date

Format	Query
USA mm/dd/yy	select convert(varchar, getdate(), 1)
ANSI yy.mm.dd	select convert(varchar, getdate(), 2)
British/French dd/mm/yy	select convert(varchar, getdate(), 3)
German dd.mm.yy	select convert(varchar, getdate(), 4)
Italian dd-mm-yy	select convert(varchar, getdate(), 5)
dd mon yy	select convert(varchar, getdate(), 6)
Mon dd, yy	select convert(varchar, getdate(), 7)
USA mm-dd-yy	select convert(varchar, getdate(), 10)
JAPAN yy/mm/dd	select convert(varchar, getdate(), 11)
ISO yymmdd	select convert(varchar, getdate(), 12)
mon dd yyyy hh:miAM (or PM)	select convert(varchar, getdate(), 100)
mm/dd/yyyy	select convert(varchar, getdate(), 101)
yyyy.mm.dd	select convert(varchar, getdate(), 102)
dd/mm/yyyy	select convert(varchar, getdate(), 103)
dd.mm.yyyy	select convert(varchar, getdate(), 104)
dd-mm-yyyy	select convert(varchar, getdate(), 105)
dd mon yyyy	select convert(varchar, getdate(), 106)
Mon dd, yyyy	select convert(varchar, getdate(), 107)
hh:mm:ss	select convert(varchar, getdate(), 108)
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)	select convert(varchar, getdate(), 109)
mm-dd-yyyy	select convert(varchar, getdate(), 110)
yyyy/mm/dd	select convert(varchar, getdate(), 111)
yyyymmdd	select convert(varchar, getdate(), 112)
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)	select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)
hh:mi:ss:mmm(24h)	select convert(varchar, getdate(), 114)

T-SQL Newline–Satır

CHAR(13) + CHAR(10) (Carriage return + Line Feed).

Truncate Table SQL CF desteklenmiyor

aşağıdakinin yerine CF de

TRUNCATE TABLE [NAMEOFTABLE];

bunu

DELETE FROM [NAMEOFTABLE];

ALTER TABLE [NAMEOFTABLE] ALTER COLUMN ID IDENTITY (1,1);

 

yazmak gerekiyor. aynı şekilde CE cihazlarda .net 3.5 ‘da multithread kod yazarken MethodInvoker nesnenesi yok ve yerine ThreadStart ile casting yapabiliyoruz.

 

if (mControl.InvokeRequired)

{

    mControl.Invoke((System.Threading.ThreadStart)delegate {

        showProgressBar(Progress);

       });

}

voila

Choosing Between SQL Server 2005 Compact Edition and SQL Server 2005 Express Edition

Feature	SQL Server Compact Edition	SQL Server Express Edition
Deployment/Installation Features
Installation size	1.7mb download size 1.8mb expanded on disk	53.8mb download size ~197mb expanded on disk
ClickOnce deployment	+	+
Privately installed, embedded, with the application	+	-
Non-admin installation option	+	-
Runs on Windows Mobile platform	+	-
Installed centrally with an MSI	+	+
Runs in-process with application	+	-
64-bit support	- Version 3.1           + Native 64 in next version	+ Windows on Windows (WOW)
Runs as a service	+ In process with the application	+ N + 1
Data file features
File format	Single file	Multiple files
Data file storage on a network share	+	-
Support for different file extensions	+	-
Database size support	4GB	4GB
XML storage	+ stored as nText	+
Code free, document safe, file format	+	-
Programmability
Transact-SQL Common Query Features	+	+
Procedural T-SQL Select Case, If, features	-	+
Remote Data Access (RDA)	+	-
ADO.NET Sync Framework	+ Enabled with Visual Studio Orcas	- Planed support with a future version
Subscriber for merge replication	+	+
Simple transactions	+	+
Distributed transactions	-	+
Native XML, XQuery/QPath	-	+
Stored procedures, views, triggers	-	+
Role-based security	-	+
Number of concurrent connections	256	Unlimited

Çalışan Sorguları Listeleme- Find Running Queries

 

SELECT      sqltext.TEXT,

            req.session_id,

            req.status,

            req.command,

            req.cpu_time,

            req.total_elapsed_time

                  FROM sys.dm_exec_requests req

                  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

 

dönen kayıtlarda gözüken session_id kullanılarak saorgu iptal edilebilir/kesilebilir.

KILL [session_id]

Log dosyası geçmişinin temizlenmesi

          

 ALTER DATABASE [DATABASE_ADI] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(DATABASE_log_dosyasi, 1)

ALTER DATABASE [DATABASE_ADI] SET RECOVERY FULL WITH NO_WAIT

BI Report Server Oran Hesaplama

Public Function OranHesapla(ByVal currentValue As Double, ByVal totalValue As Double) As Double
    If totalValue =0 Then
        Return 0
    Else
        Return (currentValue -totalValue ) / totalValue
    End If
End Function

Public Function OranYuzde(ByVal currentValue As Double, ByVal totalValue As Double) As Double
    If currentValue =0 Then
        Return 0
    Else
        Return (totalValue  / currentValue)*100
    End If
End Function

rdl Code örneği

=Code.OranYuzde(SUM(Fields!GYT_SATIS.Value),SUM(Fields!BK_SATIS.Value))

SQL ile değerlerin arasına virgül koymak- Join

SELECT STUFF((SELECT ',' + ISMI FROM MAGAZA_TANIM FOR XML PATH('')),1, 1, '')

iki tarih arasındaki zamanı hesaplamak

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString datediff2(SqlDateTime Date1, SqlDateTime Date2, string StringFormat=" {0} yıl, {1} ay, {2} gün")

    {

        // date1 is small one-- date2 is biggest one

        int years = 0;

        int months = 0;

        int days = 0;

 

        string buff = string.Empty;

 

        DateTime currentDate = Date1.Value;

        DateTime datetarget = Date2.Value;

 

 

 

        DateTime tmpMyDOB = new DateTime(datetarget.Year, datetarget.Month, 1);

        DateTime tmpFutureDate = new DateTime(currentDate.Year, currentDate.Month, 1);

 

        while (tmpMyDOB.AddYears(years).AddMonths(months) < tmpFutureDate)

        {

            months++;

            if (months > 12)

            {

                years++;

                months = months - 12;

            }

        }

 

        if (currentDate.Day >= datetarget.Day)

        {

            days = days + currentDate.Day - datetarget.Day;

        }

        else

        {

            months--;

            if (months < 0)

            {

                years--;

                months = months + 12;

            }

            days +=

                DateTime.DaysInMonth(

                    currentDate.AddMonths(-1).Year, currentDate.AddMonths(-1).Month

                ) + currentDate.Day - datetarget.Day;

 

        }

 

        //add an extra day if the dob is a leap day

        if (DateTime.IsLeapYear(datetarget.Year) && datetarget.Month == 2 && datetarget.Day == 29)

        {

            //but only if the future date is less than 1st March

            if (currentDate >= new DateTime(currentDate.Year, 3, 1))

                days++;

        }

 

 

 

        buff = string.Format(StringFormat, years, months, days);

 

        buff = buff.Replace(", 0 gün", string.Empty);

        buff = buff.Replace(", 0 ay", string.Empty);

        buff = buff.Replace(" 0 yıl, ", string.Empty);

 

        return new SqlString(buff);

    }