Gjoreski 26.08.2018 11:20
Predmet:Ms Sql Arhiva

U OVOJ TEMI STAVLJAMO MSSQL FUNKCIJE ,STOR PROCEDURE , TRIGERE , SQL Pitajna i sve ostale stvari koje ce nam biti korisne.

Gjoreski 26.08.2018 11:22
Predmet:Redni Broj

Kako dodati redni broj u query koe pravimo

SELECT ROW_NUMBER() OVER(ORDER BY ID_Korisnik ) AS Row,tblKorisnik.* From tblKorisnik

Gjoreski 27.08.2018 19:58
Predmet:Reset ID na neku tabelu

Cesto vam zatreba da se ID pole postavi na 0 ili ne neka druga vrednost

DBCC CHECKIDENT ('tblArtikli', RESEED, 0)

Budite oprezni kad koristite funkiju , nakon koristejne ove funkcije nekad ID pole zna da se cudno ponasa.

Gjoreski 27.08.2018 20:06
Predmet:Link MS ACCESS baza na MSSQL SERVER

Cesto vam zatreba da se uporede ili prebace podatke izmedzu MSSQL databaza i MS ACCESS databaza.
Ova e eden od mnogute nacini kako mozete spoiti ove dve vrste baze.

PreuzmiIzvorni kôd (SQL):
  1. EXEC sp_addlinkedserver @server='Access',
  2. @srvproduct='Access',
  3. @provider='Microsoft.Jet.OLEDB.4.0',
  4. @datasrc='D:\PROGRAMIRAJNE\TAXI MK\TAXI TDB 8X\BazaT.mdb'
  5.  
  6. -- Ovako se poziva
  7.  
  8. SELECT * FROM OPENQUERY(Access , 'select * from tblArtikli')

Gjoreski 10.11.2018 16:33
Predmet:SELECT FROM Stored Procedure

Jedan nacin kako pozvati Stored Procedura , a rezultati koristite za ponatamosna obrada

PreuzmiIzvorni kôd (SQL):
  1. CREATE TABLE #GetVersionValues
  2. (
  3.     [INDEX] INT,
  4.     [Name]  sysname,
  5.     Internal_value  INT,
  6.     Character_Value sysname
  7. )
  8. INSERT #GetVersionValues EXEC master.dbo.xp_msver 'WindowsVersion'
  9. SELECT * FROM #GetVersionValues
  10. DROP TABLE #GetVersionValues     -- Ovo je linija gde se obraduje podatke

zxz 09.12.2018 22:15
Predmet:Formati datuma

Formati datuma na MsSQL.
Format datumastnadardSQLizgled
Mon DD YYYY 1DefaultSELECT CONVERT(VARCHAR(20), GETDATE(), 100)Jan 1 2005 1:29PM 1
MM/DD/YYUSASELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]11/23/98
MM/DD/YYYYUSASELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]11/23/1998
YY.MM.DDANSISELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]72.01.01
YYYY.MM.DDANSISELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]1972.01.01
DD/MM/YYBritish/FrenchSELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]26348
DD/MM/YYYYBritish/FrenchSELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]26348
DD.MM.YYGermanSELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]25.12.05
DD.MM.YYYYGermanSELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]25.12.2005
DD-MM-YYItalianSELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]35819
DD-MM-YYYYItalianSELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]35819
DD Mon YY 1-SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]04 Jul 06 1
DD Mon YYYY 1-SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]04 Jul 2006 1
Mon DD, YY 1-SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]Jan 24, 98 1
Mon DD, YYYY 1-SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]Jan 24, 1998 1
HH:MM:SS-SELECT CONVERT(VARCHAR(8), GETDATE(), 108)0.142280092592593
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1Default + SELECT CONVERT(VARCHAR(26), GETDATE(), 109)Apr 28 2006 12:32:29:253PM 1
MM-DD-YYUSASELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]38718
MM-DD-YYYYUSASELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]38718
YY/MM/DD-SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]98/11/23
YYYY/MM/DD-SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]36122
YYMMDDISOSELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]980124
YYYYMMDDISOSELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1Europe default + millisecondsSELECT CONVERT(VARCHAR(24), GETDATE(), 113)28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H)-SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)ODBC CanonicalSELECT CONVERT(VARCHAR(19), GETDATE(), 120)26299.5711111111
YYYY-MM-DD HH:MI:SS.MMM(24h)ODBC CanonicalSELECT CONVERT(VARCHAR(23), GETDATE(), 121)26348.2745889931
 (with milliseconds)  
YYYY-MM-DDTHH:MM:SS:MMMISO8601SELECT CONVERT(VARCHAR(23), GETDATE(), 126)1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1KuwaitiSELECT CONVERT(VARCHAR(26), GETDATE(), 130)28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAMKuwaitiSELECT CONVERT(VARCHAR(25), GETDATE(), 131)28/04/2006 12:39:32:429AM

zxz 09.12.2018 22:30
Predmet:Formati datuma

Formati datum Na MsSQL bazi:
DatumSQLizgled
YY-MM-DDSELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]99-01-24
YYYY-MM-DDSELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]36184
MM/YYSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] 36373
MM/YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]38687
YY/MMSELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]99/08
YYYY/MMSELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]2005/12
Month DD, YYYY 1SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]July 04, 2006 1
Mon YYYY 1SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]Apr 2006 1
Month YYYY 1SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]February 2006 1
DD Month 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]11 September 1
Month DD 1SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]September 11 1
DD Month YY 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]19 February 72 1
DD Month YYYY 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]11 September 2002 1
MM-YYSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]33939
 SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] 
MM-YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]38838
YY-MMSELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]92/12
YYYY-MMSELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]2006-05
MMDDYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]122506
MMDDYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]12252006
DDMMYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]240702
DDMMYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]24072002
Mon-YY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]Sep-02 1
Mon-YYYY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]Sep-2002 1
DD-Mon-YY 1SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]25-Dec-05 1
DD-Mon-YYYY 1SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]38711

Gjoreski 22.12.2018 20:54
Predmet:Cross Apply

Cesto nam zatreba rezultat iz neka funkcija da vezemo za neka tablica , a neko pole iz tablice da bude kriterium u funkcija .
Jedan od nacina da dodzemo do zeljeni rezultat nam omogucava naredba CROSS APPLY

Jedan Primer :
Imame tablica tblUsers i u njoj polja ID_User , uUsername,uPassword
imamo i funkcija fnProracun koja nam vraca proracun radni dana nekog Usera uslov za rad na ta funkcija je ID_User.

onda SQL bi izgledao ovako:

SELECT U.ID_User , U.uUsername,U.uPassword,rezRadni_Dani FROM tblUsers U
CROSS APPLY fnProracun( U.ID_User)

Gjoreski 27.05.2019 15:20
Predmet:Datumi i Vreme

PreuzmiIzvorni kôd (SQL):
  1. -- Samo formate za Datum    
  2.         SELECT CONVERT(VARCHAR, getdate(), 1)   --  12/30/06
  3.         SELECT CONVERT(VARCHAR, getdate(), 2)   --  06.12.30
  4.         SELECT CONVERT(VARCHAR, getdate(), 3)   --  30/12/06
  5.         SELECT CONVERT(VARCHAR, getdate(), 4)   --  30.12.06
  6.         SELECT CONVERT(VARCHAR, getdate(), 5)   --  30-12-06
  7.         SELECT CONVERT(VARCHAR, getdate(), 6)   --  30 Dec 06
  8.         SELECT CONVERT(VARCHAR, getdate(), 7)   --  Dec 30, 06
  9.     SELECT CONVERT(VARCHAR, getdate(), 10)      --  12-30-06
  10.     SELECT CONVERT(VARCHAR, getdate(), 11)      --  06/12/30
  11.     SELECT CONVERT(VARCHAR, getdate(), 12)      --  061230
  12.         SELECT CONVERT(VARCHAR, getdate(), 23)  --  2006-12-30
  13.         SELECT CONVERT(VARCHAR, getdate(), 101) --  12/30/2006
  14.         SELECT CONVERT(VARCHAR, getdate(), 102) --  2006.12.30
  15.         SELECT CONVERT(VARCHAR, getdate(), 103) --  30/12/2006
  16.         SELECT CONVERT(VARCHAR, getdate(), 104) --  30.12.2006
  17.         SELECT CONVERT(VARCHAR, getdate(), 105) --  30-12-2006
  18.         SELECT CONVERT(VARCHAR, getdate(), 106) --  30 Dec 2006
  19.         SELECT CONVERT(VARCHAR, getdate(), 107) --  Dec 30, 2006
  20.         SELECT CONVERT(VARCHAR, getdate(), 110) --  12-30-2006
  21.         SELECT CONVERT(VARCHAR, getdate(), 111) --  2006/12/30
  22.         SELECT CONVERT(VARCHAR, getdate(), 112) --  20061230
  23. -- Samo formate za Vreme
  24.         SELECT CONVERT(VARCHAR, getdate(), 8)           --  00:38:54
  25.         SELECT CONVERT(VARCHAR, getdate(), 14)  --  00:38:54:840
  26.         SELECT CONVERT(VARCHAR, getdate(), 24)  --  00:38:54
  27.         SELECT CONVERT(VARCHAR, getdate(), 108) --  00:38:54
  28.         SELECT CONVERT(VARCHAR, getdate(), 114) --  00:38:54:840
  29. --Datum i Vreme
  30.         SELECT CONVERT(VARCHAR, getdate(), 0)           --  Dec 12 2006 12:38AM
  31.         SELECT CONVERT(VARCHAR, getdate(), 9)           --  Dec 30 2006 12:38:54:840AM
  32.         SELECT CONVERT(VARCHAR, getdate(), 13)  --  30 Dec 2006 00:38:54:840AM
  33.         SELECT CONVERT(VARCHAR, getdate(), 20)  --  2006-12-30 00:38:54
  34.         SELECT CONVERT(VARCHAR, getdate(), 21)  --  2006-12-30 00:38:54.840
  35.         SELECT CONVERT(VARCHAR, getdate(), 22)  --  12/30/06 12:38:54 AM
  36.         SELECT CONVERT(VARCHAR, getdate(), 25)  --  2006-12-30 00:38:54.840
  37.         SELECT CONVERT(VARCHAR, getdate(), 100) --  Dec 30 2006 12:38AM
  38.         SELECT CONVERT(VARCHAR, getdate(), 109) --  Dec 30 2006 12:38:54:840AM
  39.         SELECT CONVERT(VARCHAR, getdate(), 113) --  30 Dec 2006 00:38:54:840
  40.         SELECT CONVERT(VARCHAR, getdate(), 120) --  2006-12-30 00:38:54
  41.         SELECT CONVERT(VARCHAR, getdate(), 121) --  2006-12-30 00:38:54.840
  42.         SELECT CONVERT(VARCHAR, getdate(), 126) --  2006-12-30T00:38:54.840
  43.         SELECT CONVERT(VARCHAR, getdate(), 127) --  2006-12-30T00:38:54.840
  44. --Islamski Kalendar
  45.         SELECT CONVERT(nvarchar, getdate(), 130) --     date output
  46.         SELECT CONVERT(nvarchar, getdate(), 131) --     10/12/1427 12:38:54:840AM
  47. --ZAMENA NA KARAKTERA ( /,) SA PRAZEN STRING  Vraca samo broj
  48.     SELECT REPLACE(CONVERT(VARCHAR, getdate(),101),'/','')                                                    -- 12302006
  49.     SELECT REPLACE(CONVERT(VARCHAR, getdate(),101),'/','') + REPLACE(CONVERT(VARCHAR, getdate(),108),':','')  -- 12302006004426

Gjoreski 31.08.2021 22:28
Predmet:Konekcije i IP na Databazu

PreuzmiIzvorni kôd (SQL):
  1. SELECT
  2.     DB_NAME(dbid) AS DBName,
  3.     COUNT(dbid) AS NumberOfConnections,
  4.     loginame AS LoginName
  5. FROM
  6.     sys.sysprocesses
  7. WHERE
  8.     dbid > 0
  9. GROUP BY
  10.     dbid, loginame
  11.  
  12. SELECT session_id, client_net_address
  13.   FROM sys.dm_exec_connections