T-SQL to Convert CYYMMDD to Standard Date

  

 --For date between 1900 and 2099
--CYYMMDD
--7 digits
select   Cast(stuff('1170226',1,1,'20') as date) 
-- or
,  Cast(stuff('1170226',1,1
,Case When right('1170226',1)=1 then '20' When right('1170226',1)=0 then '19' End) 
as date) 


 

Binary to Bigint Sample

  

 Declare @bStr varchar(16)='09EEDF7C10C41030'


  
 Select Convert(BIGINT,0x09EEDF7C10C41030) --715755114748710960

 --Use CONVERT function with style 2 to data type BIGINT

Select  Convert(bigint, CONVERT(VARBINARY(8),  @bStr,2))  

--check
Select   CONVERT(BINARY(8), CONVERT(BIGINT, 715755114748710960))  
--0x09EEDF7C10C41030

--Below returns wrong value
Select  Cast('0x'+@bStr as binary(8))  ---wrong



 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ef5cd089-71c9-4ded-af69-8682d77595ed/hexadecimal-to-number-table-values-functionsp?forum=transactsql

http://stackoverflow.com/questions/33021992/i-want-to-get-hexadecimal-value-off-a-binary-value-removing-0x-from-it


Winscp Script to Rename a File to Append Timestamp

Based on a link from Stackoverflow by WinSCP author Martin Prikryl:
The put command accepts an optional second argument to specify a target name.
put localfile remotefile

http://stackoverflow.com/questions/41465186/batch-script-to-rename-file-with-timestamp-on-ftp-server-after-upload

Here is an example I used:

winscp.StandardInput.WriteLine(“put E:\\myfolder\\myfile.csv myfile” + DateTime.Now.ToString(“_MMddyyyy_HHmm”) + “.csv”);
The local file name is myfile.csv and the remote file name changed to
myfile_03202017_1413.csv

//Based on WinSCP site forum, Martin suggested: mv command should work as well
//winscp.StandardInput.WriteLine(“mv myfile.csv myfile”+ DateTime.Now.ToString(“_MMddyyyy_HHmm”) + “.csv”);


Powershell with Literal Path

When folder name has [], you can escape them with two back tick “ to escape them to create a file by using the path with special characters.

  
  
 
 
 
New-Item  –Path "c:temp\``[Profile Backup``]\$(Get-Date -Format yyyy_MM_dd)\" -Name "Installed_Programs1.txt" -ItemType File -Force

New-Item  –Path "c:temp\``[Profile Backup``]\2017_03_17\" -Name "Installed_Programs1.txt" -ItemType File -Force
 
 
 ##create the folder(don't need escape), then create the file
md -Path "c:temp\[Profile Backup]\$(Get-Date -Format yyyy_MM_dd)" -Force
New-Item  –Path "c:temp\``[Profile Backup``]\$(Get-Date -Format yyyy_MM_dd)\" -Name "Installed_Programs1.txt" -ItemType File -Force

 

https://technet.microsoft.com/en-us/library/ff730956.aspx

https://social.technet.microsoft.com/Forums/windows/en-US/27c62029-49c2-4170-864a-9c3781737a64/newitem-not-creating-file-why-not?forum=winserverpowershell


WASP Bar Code Reader (MCS 3950) Unofficial Setting to Read One Digit

By default, WASP Barcode ReaderWCS 3950 does not support 1 digit with Code128 barcode based on its User Manual.

But I found a link to have a workaround:
http://support.waspbarcode.com/index.php?/Knowledgebase/Article/View/884/74/program-the–wlr2900-wcs3900-and-wlr8900-series-scanners-to-read-pre-printed-barcodes

A link titled as “Code 128 Minimum Length Programming Sheet” has barcode sources to update scanner setting.

The source barcode for Minimum Length barcode, 0 barcode and 1 barcode was downloaded from below link:
http://dl.waspbarcode.com/wasp/supportfiles/WLR2900,WCS3900,WLR8900MinLength.pdf
After print this PDF page and do the following
1. Scan the Minimum Length barcode (Code 128).
2. Scan the 0 barcode then the 1 barcode.
3. Scan the Minimum Length barcode again.

You should be able to scan one digit barcode generated with code 128 now.


Export Flat File from SQL Server for AS400 System to Consume (with Code page 65001(UTF-8) )

I have a client request for a csv file from our system to consume in their AS400 system.

The request was side-tracked by to remove the last non-visible line at the end of the file.

The real problem is the encoding issue. The default encoding from our Windows (2012) system is 1252(ANSI -Latin I).
First I tried Code Page 437 but it didn’t work.

Finally I switched the encoding to Code page 650001(UTF-8) from the Flat File Connection Manager,
and it seems the file works in the third party environments.


Dynamic Matrix (UNPIVOT and PIVOT)

  
 
create TABLE pivot_information  (
month_col VARCHAR(50),
[scrapRate] int,
[surfaceRepairRate] int,
[totalRepairRate] int ,
[totalProduction] int,
[totalSurfaceParts] int,
[totalScrapNumber] INT ,
[surfaceRepairingNumber] int ,
[allRepairingNumber] int

)

INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'jan', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'feb', 6.58, 20.85,85.12,35652,4521,502,400,1002)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'march', 6.58, 20.85,85.12,35653,4521,503,400,1003)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'april', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'may', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'june', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'july', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'august', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'september', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'october', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'november', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'december', 6.58, 20.85,85.12,35651,4521,500,400,1000)

 



Declare @sql1 as NVarchar(4000)
Declare @sql2 as NVarchar(4000)
Declare @Cols1 as NVarchar(4000)
Declare @Cols2 as NVarchar(4000)
 
Set @Cols1=null
 
Select @Cols1 =  COALESCE(@Cols1 + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
FROM  [INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME='pivot_information' and COLUMN_NAME<>'month_col'

  --Print @Cols1

  Select @sql1=';with mycte as (Select
month_col, col,val
FROM pivot_information t
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val) )
SELECT   @Cols2  = STUFF( (SELECT  '','' + ''Max(CASE WHEN month_col='' + quotename(month_col,'''''''') + '' THEN val else null end ) as '' + quotename(month_col)  + char(10)+char(13)
                             FROM mycte
							 Group by month_col
							 order by Case 
							 when month_col=''Jan'' then 1
							  when month_col=''Feb'' then 2
							   when month_col=''March'' then 3
							    when month_col=''APril'' then 4
								 when month_col=''May'' then 5
								  when month_col=''June'' then 6
								   when month_col=''July'' then 7
								   when month_col=''August'' then 8
								   when month_col=''September'' then 9
								   when month_col=''October'' then 10
								    when month_col=''November'' then 11
									 when month_col=''December'' then 12
									 End							 
  FOR XML PATH(''''), TYPE).value(''.'', ''varchar(max)''), 1, 1, ''''); '
 
 

 EXEC sp_executesql @sql1,N'@Cols2 NVARCHAR(4000) output',@Cols2  output


  
 Set @sql2  =N';with mycte as (Select month_col, col,val
FROM pivot_information t
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val) )
Select  col, '+   @Cols2 + '  from  mycte   Group by col';

EXEC sp_executesql @sql2

 
drop table pivot_information


 

https://social.msdn.microsoft.com/Forums/en-US/9e915c1e-ec50-45c9-925b-5a2b89458cc8/conveting-rows-to-columns-vice-versa-in-sql-server?forum=transactsql