MS SQL Server — генерация года и месяца в SQL-query

Случай, когда нужно сгенерировать год и месяц, используя только SQL-query:

SELECT SS.YYYY, TT.MM FROM
(SELECT 10*10*(a-1) + 10*(b-1) + (c-1) + 2000 AS YYYY FROM
(SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) x
CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) y
CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) z
WHERE 10*10*(a-1) + 10*(b-1) + (c-1) <= (YEAR(GETDATE()) - 2000)) SS
,
(SELECT 10*10*(a-1) + 10*(b-1) + c AS MM FROM
(SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) x
CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) y
CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) z
WHERE 10*10*(a-1) + 10*(b-1) + c <= 12) TT
ORDER BY SS.YYYY, TT.MM

YYYY  MM
.....      ....
2013    10
2013    11
2013    12
2014    1
2014    2
2014    3
2014    4
2014    5
2014    6
2014    7
2014    8
2014    9
2014    10
2014    11
2014    12
Реклама

Write the result of SQL-query in file with encoding UTF-8 without BOM

Скрипт позволяет выгрузить результат SQL-запроса (MS SQL Server) в текстовый файл с разделителями в кодировке UTF-8 без BOM.

Dim oSource
Dim oDatabase
Dim oUser
Dim oPassword
Dim conn
Dim cmd
Dim rs
Dim fs
Dim arr
Dim textStream
Dim FileName
Dim strLine, Str
Dim Res
Dim sep
Dim i
Dim dblQuate

'Enter settings for connecting to MS SQL database
oSource = "mssqlsrv"
oDatabase = "hwiproducts"
oUser = "hwiuser"
oPassword = "*******"

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=" _
    & oSource & ";Trusted_Connection=Yes;Initial Catalog=" _
    & oDatabase & ";User ID=" & oUser & ";Password=" & oPassword & ""

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn

'Place your SQL-Query
cmd.CommandText = "SELECT TYPENAME, " _
                 & "MDNAME, " _
                 & "SN, " _
                 & "STRIHCODE, " _
                 & "VENDOR, " _
                 & "convert(varchar(20), INDEMNITYD, 120) INDEMNITYD, " _
                 & "convert(varchar(20),GUARANT,120) GUARANT, " _
                 & "DEP " _
                 & "FROM [hwiproducts].[dbo].[productstores]" _
                 & "WHERE TYPEEQU = 'Printers'"

'Execute your SQL-Query
Set rs = cmd.Execute
 
'Specify the full path to the file to which we write the query result
FileName = "d:\PLANTRACE\HWI\Printers.csv"
 
Set fs = CreateObject("Scripting.FileSystemObject")

'Check the existence of the file and delete it (if necessary)
If fs.FileExists(FileName) Then
   fs.DeleteFile(FileName)
End If

Set textStream = fs.OpenTextFile(FileName, 8, True)

'Specifies the separator
sep = ","

Do Until rs.EOF
    
    'Count the number of fields
    ColCount = rs.Fields.Count - 1
    
    For i = 0 To ColCount
        'Add double quotes to values (if necessary)
        dblQuate = """"& rs(rs.Fields(i).Name) &""""
        
        'Form a string to write to the file
        strLine = strLine & sep & dblQuate
        
        If i = ColCount Then
            
            'Separator to remove from the beginning of the string
            Str = Replace(strLine,",","",1,1)
            
            'Convert string to UTF-8 without BOM and write it in file
            Res = StrConvert (Str, "Windows-1251", "UTF-8")
            textStream.WriteLine Res
            
            'When a string is writed then clean it
            strLine = ""
        End If
    Next
    
    'Next row from result query
    rs.MoveNext
Loop

'Close stream, command and connecting
textStream.Close
rs.Close
conn.Close

Function StrConvert(Text, FromCharset, ToCharset)
'What's: converts the string in encoding UTF8 without BOM
Dim Stream

Set Stream = CreateObject("ADODB.Stream")
    Stream.Type = 2
    Stream.Mode = 3
    Stream.Open
    Stream.Charset = ToCharset
    Stream.WriteText Text
    Stream.Position = 0
    Stream.Charset = FromCharset
    BOM = Stream.ReadText(3)

'Skip BOM bytes
If AscB(MidB(BOM, 1, 1)) = 239 And AscB(MidB(BOM, 2, 1)) = 187 _
                             And AscB(MidB(BOM, 3, 1)) = 191 Then
    Stream.Position = 3
 Set fOut = CreateObject("adodb.stream")
    fOut.Type = 2
    fOut.Mode = 3
    fOut.Open
    fOUT.WriteText Text
    StrConvert = fOUT.ReadText
Else
    StrConvert = Stream.ReadText
End If
 
End Function

Миграция структуры и данных MySQL на MS SQl Server при помощи SSMA

  • SSMA — Microsoft Sql Server Migration Assistant

1. Где взять SSMA?
http://www.microsoft.com/en-us/download/details.aspx?id=28764

2. Как инсталлировать SSMA?
After the download, you must extract the installation files before you can install SSMA for MySQL.

Installing the SSMA for MySQL

  1. Double-click SSMA for MySQL exe.
  2. On the ‘Welcome’ page, click Next.
  3. If you do not have the prerequisites installed, a message will appear that indicates that you must first install required components. Make sure that you have installed all prerequisites, and then run the installation program again.
  4. Read the End User License Agreement. If you agree to the terms, select «I accept the agreement» option and click Next.
  5. Read the ‘Usage Report Settings’ page, select or clear the feature reporting box, and then click Next.
  6. On the ‘Choose Setup Type’ page, click Typical.
  7. Click Install.

In addition to the SSMA program files, you must also install the SSMA for MySQL Extension Pack on the SQL Server machine.

Installing the SSMA for MySQL Extension Pack
Note: SSMA for MySQL Extension Pack is not supported on Windows XP.

  1. Double-click SSMA for MySQL Extension Pack.exe file.
  2. On the ‘Welcome’ page, click Next.
  3. Read the End User License Agreement. If you agree to the terms, select «I accept the agreement» option and click Next.
  4. On the ‘Choose Setup Type’ page, click Typical.
  5. On the ‘Ready to Install’ page, click Install.
  6. On the ‘Completed the First Step of Installation’ page, click Next. A new dialog box will appear, in which you select the instance of SQL Server for the extension pack installation.
  7. Select the instance of SQL Server where you will be migrating MySQL schemas, and then click Next. The default instance has the same name as the computer. Named instances will be followed by a backslash and the instance name.
  8. On the connection page, select the authentication method and then click Next.Windows Authentication will use your Windows credentials to try to log on to the instance of SQL Server. If you select SQL Server Authentication, you must enter a SQL Server login name and password.
  9. On the next page, select Install Utilities Database n, where n is the version number, and then click Next.The sysdb database is created and the user-defined functions and stored procedures are created in that database. If Install Tester Database option is checked the tester ssmatesterdb database will be created.
  10. To install the utilities to another instance of SQL Server, select Yes, and then click Next. Or, to exit the wizard, click No.

3. Как запустить SSMA?
При первом запуске программы откроется окно, где будет предложено зайти на сайт и скачать лицензии для подключаемых баз, в нашем случае это MySQL. Заходим на сайт, качаем файл лицензии и в окне указываем путь к файлу, потом жмем Refresh license. Готово.

4. Как это работает?
http://technet.microsoft.com/ru-ru/library/ff520606.aspx

5. Если во время операций «Covert schema» или «Synchronize with the Database» возникли ошибки связанные с cascade foreign keys?
http://blogs.msdn.com/b/ssma/archive/2011/03/19/mysql-to-sql-server-migration-method-for-correcting-schema-issues.aspx

ManageEngine ServiceDesk Plus часовой пояс, формат даты и время

  • ManageEngine ServiceDesk Plus 8, 9
  • база данных на MS SQL Server

Для индивидуального изменения параметров часового пояса, формата даты и время нужно:
> зайти в веб-клиент ManageEngine ServiceDesk Plus
> перейти в Личные настройки и там выбрать нужный часовой пояс и формат даты и время.

Если нужно быстро сменить эти параметры для всех пользователей  ManageEngine ServiceDesk Plus, тогда следует подключиться к базе данных (в моем случае это servicedesk (MS SQL Server 2012)) и выполнить команду:

use servicedesk
go
update dbo.sduserprofile
set TIMEZONEID = <TIMEZONEID>,
    DATE_FORMAT = <DATE_FORMAT>,
    TIME_FORMAT = <TIME_FORMAT>
go

<TIMEZONEID> - выбираем из таблицы TimezoneDefinition,
соответствующий нужному нам Часовому поясу
<DATE_FORMAT> - формат даты, например, 'yyyy.MM.dd'
<TIME_FORMAT> -  формат время, например, 'dd.MM.yyyy HH:mm'</span>

Пример
Нужно всем поменять Часовой пояс на (GMT +3:00) Further-Eastern European Time Europe/Minsk, формат Даты на yyyy.MM.dd и Время на dd.MM.yyyy HH:mm. В таблице TimezoneDefinition этому часовому поясу соответствует TIMEZONEID = 301. Далее выполняем:

use servicedesk
go
update dbo.sduserprofile
set TIMEZONEID = 301,
    DATE_FORMAT = 'yyyy.MM.dd',
    TIME_FORMAT = 'dd.MM.yyyy HH:mm'
go

Также может оказаться, что в базе данных ServiceDesk отсутствуют записи о пользователях в таблице профилей dbo.sduserprofile. В этом случае, чтобы таким пользователям изменить часовой профиль не заходя к ним в веб-клиент, нужно создать записи в таблице профилей с ссылками на dbo.aaauser. Для это нужно выполнить следующий код:

insert into dbo.sduserprofile
(USERID,
TIMEZONEID,
DATE_FORMAT,
TIME_FORMAT,
SIGNATURE)
select au.USER_ID,
       301,
       'yyyy.MM.dd',
       'dd.MM.yyyy HH:mm',
       NULL
from dbo.aaauser au
     left join dbo.sduserprofile sd on au.USER_ID = sd.USERID
     inner join dbo.aaalogin lg on au.USER_ID = lg.USER_ID
where sd.USERID is NULL;

MS SQL WORKTIME — расчет рабочего времени

Я рассматривал случай рабочего дня с 08:00 по 17:00, включая обед с 12:00 по 13:00. Функция рассчитывает количество часов,
которые попадают в заданный диапазон времени с учетом выходных дней (суббота и воскресенье), рамок рабочего дня и обеденного времени. Исправил одно исключение в расчете и добавил проверку праздничных дней для Беларуси.

CREATE FUNCTION [worktime] (@beg_dt datetime, @end_dt datetime)
RETURNS FLOAT
with schemabinding
AS

BEGIN

declare @beg_datetime datetime
declare @end_datetime datetime
declare @kol_day INT
declare @res FLOAT
declare @dt FLOAT
declare @i INT

set @beg_datetime = @beg_dt;
set @end_datetime = @end_dt;
set @dt = 0;
set @res = 0;
set @i = 0;
      
    IF @beg_datetime < @end_datetime
    BEGIN
        --Расчет количества дней в заданном периоде дат
       set @kol_day = datediff(day, @beg_datetime, @end_datetime);
 
        WHILE (@i <= @kol_day)
        BEGIN
          -- Проверка выходных дней и праздничные дни
            if not datepart(w, dateadd(day, @i, @beg_datetime)) in (7, 1)
            OR  ( convert(date, dateadd(day, @i, @beg_datetime)) IN (
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-01-01'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-01-07'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-03-08'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-04-29'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-05-01'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-05-09'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-07-04'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-11-07'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-12-25')) )
            
            BEGIN
                -- Расчет количества рабочего времени по каждому дню
                set @res = case       
                -- если промежуточный день
                when not cast(dateadd(day, @i, @beg_datetime) as date) in (cast(@beg_datetime as date), cast(@end_datetime as date))
                    then 8
                -- если даты заданного дипазона совпадают
                when @kol_day = 0 and cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) then
                cast(datediff(second, dateadd(day, @i, @beg_datetime), @end_datetime) as float)/60/60
                else case
                    -- если начало периода
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@beg_datetime as date) and
                    datepart(hh, @beg_datetime) < 8
                        then 8
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@beg_datetime as date) and
                    datepart(hh, @beg_datetime) >= 8 and datepart(hh, @beg_datetime) < 12
                        then cast(datediff(second, @beg_datetime, dateadd(hour, 17, cast(cast(@beg_datetime as date) as datetime))) as float)/60/60 - 1
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@beg_datetime as date) and
                    datepart(hh, @beg_datetime) >= 13 and datepart(hh, @beg_datetime) < 17
                        then cast(datediff(second, @beg_datetime, dateadd(hour, 17, cast(cast(@beg_datetime as date) as datetime))) as float)/60/60
                    -- если конец периода
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) and
                    datepart(hh, @end_datetime) >= 8 and datepart(hh, @end_datetime) < 12
                        then cast(datediff(second, dateadd(hour, 8, cast(cast(@end_datetime as date) as datetime)), @end_datetime) as float)/60/60
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) and
                    datepart(hh, @end_datetime) >= 13 and datepart(hh, @end_datetime) < 17
                        then cast(datediff(second, dateadd(hour, 9, cast(cast(@end_datetime as date) as datetime)), @end_datetime) as float)/60/60
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) and
                    datepart(hh, @end_datetime) > 17
                        then 8
                    -- ошибка
                    else 0
                    END
                END
            -- Расчет суммароного рабочего времени за период
            set @dt = @dt + @res;

            END
            set @i = @i + 1;
        END
    END
    ELSE
        set @dt = -1;

RETURN @dt

END

Пример вызова функции:

select [worktime]('2014-06-02 10:20:00.000', '2014-06-15 16:45:37.000') dt
dt
---
77,6666666666667

MSSQL date and time functions

Несколько часто применяемых функций даты и времени в MSSQL.

CREATE FUNCTION [BEGDAY] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает начало дня: 2014-05-19 00:00:00.000 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=cast(cast(@DAT as date) as datetime)
	RETURN @RET
END
GO

CREATE FUNCTION [ENDDAY] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает конец дня: 2014-05-19 23:59:59.997 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=(convert(datetime, convert(varchar, @DAT, 101) + ' 23:59:59.998'))
	RETURN @RET
END
GO

CREATE FUNCTION [BEGMONTH] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает начало месяца: 2014-05-01 00:00:00.000 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=convert(date,dateadd(day,1-day(@DAT),@DAT))
	RETURN @RET
END
GO

CREATE FUNCTION [ENDMONTH] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает конец месяца: 2014-05-31 00:00:00.000 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=convert(datetime, convert(varchar, dateadd(month,1,dateadd(day,1-day(@DAT),@DAT))-1, 101))
	RETURN @RET
END
GO

Примеры вызова функций:

select [BEGDAY] ('2014-04-23 09:45:21') dt
select [BEGDAY] ('2014-04-23') dt
dt
--
2014-04-23 00:00:00.000

select [ENDDAY] ('2014-04-23 09:45:21') dt
select [ENDDAY] ('2014-04-23') dt
dt
--
2014-04-23 23:59:59.997

select [BEGMONTH] ('2014-04-23 09:45:21') dt
select [BEGMONTH] ('2014-04-23') dt
dt
--
2014-04-01 00:00:00.000

select [ENDMONTH] ('2014-04-23 09:45:21') dt
select [ENDMONTH] ('2014-04-23') dt
dt
--
2014-04-30 00:00:00.000

Convert Seconds to Hours and Minutes and Seconds

Данная функция позволяет преобразовать количество секунд в количество часов : минут : секунд.

CREATE FUNCTION [SEC2HMS] (@SEC FLOAT)
RETURNS VARCHAR(30)
with schemabinding
AS
/* Переводит секунды в часы:минуты:секунды */
BEGIN
declare @Hours FLOAT, @Minutes FLOAT, @Seconds FLOAT
declare @vHours VARCHAR(20), @vMinutes VARCHAR(2), @vSeconds VARCHAR(2)

set @SEC=@SEC;
set @Hours = FLOOR(@SEC/60/60);
set @Minutes = FLOOR((@SEC/60/60 - FLOOR(@SEC/60/60))*60);
set @Seconds = FLOOR((((@SEC/60/60 - FLOOR(@SEC/60/60))*60) - FLOOR((@SEC/60/60 - FLOOR(@SEC/60/60))*60))*60);

set @vHours = case when len(@Hours) = 1 then '0'+cast(@Hours as varchar(20))
				   else cast(@Hours as varchar(20)) end;
set @vMinutes = case when len(@Minutes) = 1 then '0'+cast(@Minutes as varchar(20))
				   else cast(@Minutes as varchar(20)) end;
set @vSeconds = case when len(@Seconds) = 1 then '0'+cast(@Seconds as varchar(20))
				   else cast(@Seconds as varchar(20)) end;

 RETURN @vHours + ':' + @vMinutes + ':' + @vSeconds;
end

Пример вызова функции, если переводим секунды в часы, минуты и секунды:

select SEC2HMS(3715412) as hms;
hms
--
1032:03:32

COUNT WORKING DAYS IN MONTH (количество рабочих дней в месяце)

Данная функция возвращает количество рабочих дней в месяце, который берется из указанной даты.
Выходными днями функция считает СУББОТУ (7) и ВОСКРЕСЕНЬЕ (1).

Пример вызова функции:

select WORKDAYS ('2014-02-10');
--
20
CREATE FUNCTION WORKDAYS (@DAT DATETIME)
RETURNS INT
with schemabinding
AS
/* Возвращает количество рабочих дней в месяце по указанной дате */
BEGIN
DECLARE @RET INT;
set @RET = null;

select @RET = count(*) from (
select cast(convert(varchar(4),year(@DAT)) + '-' + convert(varchar(2),month(@DAT)) + '-' + convert(varchar(2),calendar.dd) as date) dt
from (
select 1 dd union
select 2 dd union
select 3 dd  union
select 4 dd  union
select 5 dd  union
select 6 dd  union
select 7 dd  union
select 8 dd  union
select 9 dd  union
select 10 dd  union
select 11 dd  union
select 12 dd  union
select 13 dd  union
select 14 dd  union
select 15 dd  union
select 16 dd  union
select 17 dd  union
select 18 dd  union
select 19 dd  union
select 20 dd  union
select 21 dd  union
select 22 dd  union
select 23 dd  union
select 24 dd  union
select 25 dd  union
select 26 dd  union
select 27 dd  union
select 28 dd  union
select 29 dd  union
select 30 dd  union
select 31 dd ) calendar
where calendar.dd <= DAY(convert(date, convert(varchar, dateadd(month,1,dateadd(day,1-day(@DAT),@DAT))-1)))
and datepart(w,cast(convert(varchar(4),year(@DAT)) + '-' + convert(varchar(2),month(@DAT)) + '-' + convert(varchar(2),calendar.dd) as date)) NOT IN (1,7)) ap;
RETURN @RET;
END
GO