SQL TAKVIM CALISMA SAATLI

On June 25, 2019August 20, 2021 By semt20In SqlLeave a comment

  CREATE VIEW [dbo].[BM_TAKVIM] AS
WITH Calender AS (
    SELECT CAST('2019-01-01' AS DATETIME) AS dt
    UNION ALL
    SELECT dt + 1 FROM Calender
    WHERE dt + 1 <=  CAST('2029-12-31' AS DATETIME)
)
SELECT
CONVERT(VARCHAR, DATEFROMPARTS(YEAR(dt),MONTH(dt) , DAY(DT)), 112) G112,
dt




,CASE
WHEN DATENAME (MONTH,dt)='January' THEN 'Ocak'
WHEN DATENAME (MONTH,dt)='February' THEN 'Şubat'
WHEN DATENAME (MONTH,dt)='March' THEN 'Mart'
WHEN DATENAME (MONTH,dt)='April' THEN 'Nisan'
WHEN DATENAME (MONTH,dt)='May' THEN 'Mayıs'
WHEN DATENAME (MONTH,dt)='June' THEN 'Haziran'
WHEN DATENAME (MONTH,dt)='July' THEN 'Temmuz'
WHEN DATENAME (MONTH,dt)='August' THEN 'Ağustos'
WHEN DATENAME (MONTH,dt)='September' THEN 'Eylül'
WHEN DATENAME (MONTH,dt)='October' THEN 'Ekim'
WHEN DATENAME (MONTH,dt)='November' THEN 'Kasım'
WHEN DATENAME (MONTH,dt)='December' THEN 'Aralık' ELSE '' END Ay
,CASE WHEN DATENAME (WEEKDAY,dt)='Monday' THEN 'Pazartesi'
WHEN DATENAME (WEEKDAY,dt)='Tuesday' THEN 'Salı'
WHEN DATENAME (WEEKDAY,dt)='Wednesday' THEN 'Çarşamba'
WHEN DATENAME (WEEKDAY,dt)='Thursday' THEN 'Perşembe'
WHEN DATENAME (WEEKDAY,dt)='Friday' THEN 'Cuma'
WHEN DATENAME (WEEKDAY,dt)='Saturday' THEN 'Cumartesi'
WHEN DATENAME (WEEKDAY,dt)='Sunday' THEN 'Pazar' ELSE '' END Gün
,Hafta = DATEPART(WEEK, dt) ,
CASE WHEN DATENAME (WEEKDAY,dt)='Saturday'  THEN '05:00'   WHEN DATENAME (WEEKDAY,dt)='Sunday'  THEN '00:00' ELSE '09:00' END NM,
 CASE WHEN DATENAME (WEEKDAY,dt)='Saturday'  THEN '13:00'   WHEN DATENAME (WEEKDAY,dt)='Sunday'  THEN '00:00' ELSE '17:00' END MS

 FROM Calender

GO

--SELECT * FROM BM_TAKVIM WITH(NOLOCK) OPTION  ( MAXRECURSION 0 )

CALCULATE SQL ROWS

On June 17, 2019February 5, 2020 By semt20In SqlLeave a comment

/****** Object:  StoredProcedure [dbo].[CALCULATEROWS]    Script Date: 22.01.2019 15:49:46 ******/
-- EXEC [CALCULATEROWS]
SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

CREATE PROC [dbo].[CALCULATEROWS]
AS
IF NOT EXISTS
(
    SELECT TOP 1
           1
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
          AND TABLE_NAME = '_ROWCOUNTS'
)
BEGIN
    CREATE TABLE _ROWCOUNTS
    (
        TABLENAME VARCHAR(255) NULL,
        OLDROWCOUNT INT NULL,
        NEWROWCOUNT INT NULL
    );
END;

CREATE TABLE #COUNTS
(
    TABLENAME VARCHAR(255),
    ROWCOUNT_ INT
);

EXEC sp_MSforeachtable @command1 = 'INSERT #COUNTS (TABLENAME, ROWCOUNT_) SELECT "?", COUNT(*) FROM ?';

DECLARE @CRSR CURSOR;
DECLARE @TN NVARCHAR(250);
BEGIN
    SET @CRSR = CURSOR FOR
    SELECT TABLENAME
    FROM #COUNTS;

    OPEN @CRSR;
    FETCH NEXT FROM @CRSR
    INTO @TN;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        IF NOT EXISTS (SELECT TOP 1 1 FROM _ROWCOUNTS WHERE TABLENAME = @TN)
        BEGIN
            INSERT INTO _ROWCOUNTS
            (
                TABLENAME,
                OLDROWCOUNT,
                NEWROWCOUNT
            )
            VALUES
            (   @TN,
                (
                    SELECT TOP 1 ROWCOUNT_ FROM #COUNTS WHERE TABLENAME = @TN
                ),
                (
                    SELECT TOP 1 ROWCOUNT_ FROM #COUNTS WHERE TABLENAME = @TN
                ));
        END;
        ELSE
        BEGIN
            UPDATE _ROWCOUNTS
            SET OLDROWCOUNT = NEWROWCOUNT
            WHERE TABLENAME = @TN;
            UPDATE _ROWCOUNTS
            SET NEWROWCOUNT =
                (
                    SELECT TOP 1 ROWCOUNT_ FROM #COUNTS WHERE TABLENAME = @TN
                )
            WHERE TABLENAME = @TN;
        END;

        FETCH NEXT FROM @CRSR
        INTO @TN;
    END;

    CLOSE @CRSR;
    DEALLOCATE @CRSR;
END;

DROP TABLE #COUNTS;
SELECT *,
       (NEWROWCOUNT - OLDROWCOUNT) AS DIFFERENCE_
FROM _ROWCOUNTS
WHERE (NEWROWCOUNT - OLDROWCOUNT) > 0
ORDER BY DIFFERENCE_ DESC;

GO

Sql Query Excel File

On June 13, 2019August 20, 2021 By semt20In EXCEL, SqlLeave a comment

USE [master]
sp_configure 'show advanced options', 1; 
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1; 
RECONFIGURE; 
GO 




GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO 

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=F:\B.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

  SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
  'Data Source=F:\C.xls;Extended Properties=Excel 8.0')...[Sheet1$]

sql server -20122014 express download

On May 30, 2019December 19, 2019 By semt20In SqlLeave a comment

Microsoft® SQL Server® 2014 Express

https://www.microsoft.com/en-us/download/details.aspx?id=42299

https://download.microsoft.com/download/E/A/E/EAE6F7FC-767A-4038-A954-49B8B05D04EB/MgmtStudio%2032BIT/SQLManagementStudio_x86_ENU.exe

https://download.microsoft.com/download/E/A/E/EAE6F7FC-767A-4038-A954-49B8B05D04EB/MgmtStudio%2064BIT/SQLManagementStudio_x64_ENU.exe

https://download.microsoft.com/download/E/A/E/EAE6F7FC-767A-4038-A954-49B8B05D04EB/Express%2032BIT/SQLEXPR_x86_ENU.exe

https://download.microsoft.com/download/E/A/E/EAE6F7FC-767A-4038-A954-49B8B05D04EB/Express%2064BIT/SQLEXPR_x64_ENU.exe

sql server 2012 iso olan express deildir https://www.microsoft.com/en-us/download/details.aspx?id=29066

SQL SERVER DATABASEDE ISTENILEN DEĞERİ ARAMA PROSEDURE

On April 18, 2019February 24, 2020 By semt20In SqlLeave a comment

--EXEC SearchAllTables 'a' 

CREATE PROC SearchAllTables( @SearchStr nvarchar(100))
    AS
 BEGIN
     CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
        SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
        SET @TableName = ''
        SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

      WHILE @TableName IS NOT NULL
      BEGIN
        SET @ColumnName = ''
        SET @TableName = (
                            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                              FROM INFORMATION_SCHEMA.TABLES
                             WHERE TABLE_TYPE = 'BASE TABLE'
                               AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                               AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA)
                                                      + '.' + QUOTENAME(TABLE_NAME) ),
                                                  'IsMSShipped' ) = 0
                         )
      WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
              BEGIN
                        SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME))
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                        AND TABLE_NAME = PARSENAME(@TableName, 1)
                        AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                        AND QUOTENAME(COLUMN_NAME) > @ColumnName )
                         IF @ColumnName IS NOT NULL
                              BEGIN
                                        INSERT
                                          INTO #Results
                                          EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''',
                                                 LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + '
                                                 (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 )
                                END
                END
       END
SELECT ColumnName, ColumnValue
  FROM #Results
   END

SQL OTOMATIK YEDEK ALMA

On April 11, 2019December 19, 2019 By semt20In BACKUP, SqlLeave a comment

SQL SERVER 2005 ve 2008 ÜZERİNDE OTOMATİK YEDEK ALMA
SQL SERVER 2005 ve 2008 ÜZERİNDE OTOMATİK YEDEK ALMA

SQL Server üzerinde Maintenance Plans sekmesinden yeni bir plan oluşturularak otomatik yedek aldırmak için bir görev yapılır. Maintenance Plans kullanımı için SQL Server Windows Authentication modda çalıştırılmalı ve mixed modda kurulmalıdır.
Aynı zamanda SQL Server ile birlikte SQL Server Agent’ ında çalışıyor olması gerekmektedir. Maintenance Plans kullanımı için kullanıcı sysadmin yetkisinde olmalıdır.

C# SQL E TARIH AKTARIM UYGUN FORMAT

On February 19, 2019August 20, 2021 By semt20In C SHARP, SqlLeave a comment

DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")




devexpress datetime component automove to next için
masktype 2 tane var ikisinde datetimeadvancingcaret yap

        public Form1()
        {
            InitializeComponent();
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;












ercanın kodlarda
            CultureInfo tr = new CultureInfo("tr-TR");

            Thread.CurrentThread.CurrentCulture = tr;

SQL SERVER TAKVIM TURKCE

On February 15, 2019August 20, 2021 By semt20In SqlLeave a comment

  ALTER VIEW BM_TAKVIM AS
WITH Calender AS (
    SELECT CAST('2019-01-01' AS DATETIME) AS dt
    UNION ALL
    SELECT dt + 1 FROM Calender
    WHERE dt + 1 <=  CAST('2019-12-31' AS DATETIME)
)
SELECT
dt

,CASE
WHEN DATENAME (Month,dt)='January' THEN 'Ocak'
WHEN DATENAME (Month,dt)='February' THEN 'Şubat'
WHEN DATENAME (Month,dt)='March' THEN 'Mart'
WHEN DATENAME (Month,dt)='April' THEN 'Nisan'
WHEN DATENAME (Month,dt)='May' THEN 'Mayıs'
WHEN DATENAME (Month,dt)='June' THEN 'Haziran'
WHEN DATENAME (Month,dt)='July' THEN 'Temmuz'
WHEN DATENAME (Month,dt)='August' THEN 'Ağustos'
WHEN DATENAME (Month,dt)='September' THEN 'Eylül'
WHEN DATENAME (Month,dt)='October' THEN 'Ekim'
WHEN DATENAME (Month,dt)='November' THEN 'Kasım'
WHEN DATENAME (Month,dt)='December' THEN 'Aralık' ELSE '' END Ay
,CASE WHEN DATENAME (Weekday,dt)='Monday' THEN 'Pazartesi'
WHEN DATENAME (Weekday,dt)='Tuesday' THEN 'Salı'
WHEN DATENAME (Weekday,dt)='Wednesday' THEN 'Çarşamba'
WHEN DATENAME (Weekday,dt)='Thursday' THEN 'Perşembe'
WHEN DATENAME (Weekday,dt)='Friday' THEN 'Cuma'
WHEN DATENAME (Weekday,dt)='Saturday' THEN 'Cumartesi'
WHEN DATENAME (Weekday,dt)='Sunday' THEN 'Pazar' ELSE '' END Gün
,Hafta = DATEPART(WEEK, dt)  FROM Calender

 GO

  SELECT *
 FROM BM_TAKVIM WITH(NOLOCK)
 Option(MaxRecursion 0)

SQL SERVER DECIMAL TWO DIGIT

On February 12, 2019December 19, 2019 By semt20In SqlLeave a comment

CONVERT(DECIMAL(10,2),sum(AMOUNT))
CONVERT(DECIMAL(10,2),PRICE)

SQL SERVER BIRDEN FAZLA COLUMN DUBLICATE EKLEMESIN

On February 11, 2019August 20, 2021 By semt20In SqlLeave a comment

ALTER TABLE [dbo].[PDKSEXCEL]   
ADD CONSTRAINT [UQ_SICILNO_TARIH_SAAT] UNIQUE NONCLUSTERED
(
    [SICILNO],   [TARIH],[SAAT]
)

Blog of Sem

Category: Sql