Append Timestamp to File Names–Powershell

You can rename text files in a folder with a simple replace to append timestamp to file names through powershell script.
There is also another way to control file extension to change file name as well.

From Powershell window:

 --Option with  replace .txt with appended timestamp
 Get-ChildItem c:\temp\src\*.* | rename-item -NewName {$ -replace ".txt",((get-date).ToString("_yyyyMMdd_HHmmss")+".txt")}
 --Option insert timestamp before .txt extension

ls c:\temp\src\*.* | ? { !$_.PSIsContainer   -and   $_.extension -eq '.txt' } |   ren -NewName { "$($_.BaseName) $(get-date -format "yyyymm%d%H%M%S")$($_.extension) "  }  


If you want to run powershell from SSMS, you can use xp_cmdshell to call powershell.exe

  --## replace .txt with appended timestamp
xp_cmdshell 'PowerShell.exe -noprofile -command  "Get-ChildItem c:\temp\src\*.* | rename-item -NewName {$ -replace ''.txt'',((get-date).ToString(''_yyyyMMdd_HHmmss'')+''.txt'')} "'

--## insert timestamp before .txt extension
xp_cmdshell 'PowerShell.exe -noprofile -command  "ls c:\temp\src\*.* | ? { !$_.PSIsContainer  -and  $_.extension -eq ''.txt'' } |   ren -NewName { \"$($_.BaseName) $(get-date -format \"yyyymm%d%H%M%S\")$($_.extension) \"  }   "'
xp_cmdshell 'PowerShell.exe -noprofile -command  "ls c:\temp\src\*.* | ? { !$_.PSIsContainer  -and  $_.extension -eq ''.txt'' } |   ren -NewName { \"$($_.BaseName) $(get-date -format ''_yyyyMMdd_HHmmss'')$($_.extension) \"  }   "'


Remove double quotes from Export-CSV commandlet (or files with double quotes

When use default Export-CSV commandlet, the csv file are double quoted.
The following script can remove them all.

(Get-Content $file) | Foreach-Object {$_ -replace '"', ''}|Out-File $file

##Open in notepad
notepad $file

Or this version:
(GC $file) | % {$_ -replace '"', ''}  > $file

##Open in notepad
notepad $file


Import String Type Datetime through SSIS.(dd-MMM-yy )

When you import a string type datetime to a SQL datetime column, you have to manipulate input string to follow ISO format of YYYY-MM-DD to pass the value through a derived column.
You can find a detailed posting from this posting:

Sometimes, the expression can get really complicated.
The expression looks like:

((“20” + SUBSTRING(dt,8,2) + “-” + (UPPER(SUBSTRING(dt,4,3)) == “JAN” ? “01” : UPPER(SUBSTRING(dt,4,3)) == “FEB” ? “02” : UPPER(SUBSTRING(dt,4,3)) == “MAR” ? “03” : UPPER(SUBSTRING(dt,4,3)) == “APR” ? “04” : UPPER(SUBSTRING(dt,4,3)) == “MAY” ? “05” : UPPER(SUBSTRING(dt,4,3)) == “JUN” ? “06” : UPPER(SUBSTRING(dt,4,3)) == “JUL” ? “07” : UPPER(SUBSTRING(dt,4,3)) == “AUG” ? “08” : UPPER(SUBSTRING(dt,4,3)) == “SEP” ? “09” : UPPER(SUBSTRING(dt,4,3)) == “OCT” ? “10” : UPPER(SUBSTRING(dt,4,3)) == “NOV” ? “11” : UPPER(SUBSTRING(dt,4,3)) == “DEC” ? “12” : “00”) + “-” + SUBSTRING(dt,1,2)) + ” ” + REPLACE(SUBSTRING(dt,11,8),”.”,”:”) + “.” + SUBSTRING(dt,20,3))

There is another option to use a stage table or a stage column in your target table.
First, you import the string to varchar column and update the target column with converted the datetime string through T-SQL.
In this case dd-MMM-yy (i.e. 14-DEC-2016

 ---The update statement:

update targettable
Set dt = cast(Replace(dtstring,'.',':') as datetime) ; 


We design two steps within SSIS package,

Use a Sequence container 1 to use Data Flow Task to import text file to target table directly;
Use second Sequence container to Execute T-SQL Statement Task with the above Update statement.

Powershell: Loop through Multiple Servers and Export Result to a File

## server list from a string
###   $ServerList = "dev", "qa"  

## serverlist from a file
$ServerList=Get-Content C:\temp\Serverlist.txt

##The combined file
$finalfilepath = "C:\temp\filefinal.csv"
##clear the file content if it exists
if (Test-Path $finalfilepath) {Clear-Content C:\temp\filefinal.csv }

foreach ($CurrentServer in $ServerList) 

##Save one serverinstance as a file
  $csvFilePath ="C:\temp\($CurrentServer)list.csv"
  Invoke-Sqlcmd -Server $CurrentServer -Database master  -Query "SELECT @@servername as 'Server', name as 'DB', convert(varchar(8), getdate(), 1) as 'Date' from sys.databases WHERE database_id > 4 AND database_id NOT IN (select database_id from sys.dm_hadr_database_replica_states)"  -querytimeout 65534  | export-csv $csvFilePath   -NoTypeInformation
	Get-Content  $csvFilePath >>  $finalfilepath

##Open in notepad
notepad C:\temp\filefinal.csv


Datetime (Date) Data to Excel Become String Type — How to change Back as Datetime

When your export datetime data from SQL Server to excel the result excel column become text type. You cannot format it as either date/time format or custome format within excel directly.
There are a few solutions to solve this problem.
You can simply to use TXET function to format the text directly.
Use the TEXT function in cell as formula along with the formated datetime style.
For example: =TEXT(B2,”mm/dd/yyyy”) or =TEXT(A2,”yyyy-mm-dd”) (you need to copy and paste this formula the entire column ). You can choose any other valid datetime style to format the data in the column But this is not optimum since the result is still text.

There are other functions in Excel to we can use to retrieve datevalue and timevalue from the string datetime.

–yyyy-mm-dd “2017-01-16” If this value is shwing an integer (which is a date value in integer form),
you need to use built-in format function to format date to the style you need.


A simply form without using these functions is to use a + sing to add date and time together to force the concatenation and implict conversion to real datetime value.


Apply this formula to this column, the datetime now is real datetime in excel and you can use any format within Excel now.
If the data column is DATE string, we can simply use formula: =DATEVALUE(A1).

Another option is to use Data>>Text to Columns >>(delimited default tab)>>chosse Date — pick format YMD. After this conversion, you can format this column (datetime value) to the style you need.

If your query result is not large, you can copy and past the result with header directly to Excel spreadsheet and format the datetime column afterwards.

Cannot connect to SQL Server box from local SSMS as a domain user

SSPI handshake failed with error code 0x8009030c, state 14 while establishing
a connection with integrated security;
the connection has been closed. Reason: AcceptSecurityContext failed.


From the SQL Server box,Add the domain account “ifbf\iruxxxxxx” to “Access this computer from the network”
local security policy (secpol.msc)
on the SQL Server box and retry and the connection is successfully from local SSMS.

Format _MDDyy to a standard date


select *, Cast('20'+ Right(policyexpire,2)+ Left(Right('0'+policyexpire,6) ,4) as date)  

,datefromparts(2000+ cast(Right(policyexpire,2)as int),Cast(Left(Right('0'+policyexpire,6) ,2) as int) , cast(Left(Right(policyexpire,4),2) as int) )
,datefromparts(2000+  Right(policyexpire,2) , Left(Right('0'+policyexpire,6) ,2)  , Left(Right(policyexpire,4),2)   )

 ,CAST(STUFF(STUFF(Right('0'+policyexpire,6),3,0,'/'),6,0,'/') as DATE)
 ,CONVERT(DATE,STUFF(STUFF(Right('0'+policyexpire,6),3,0,'/'),6,0,'/') )

,CONVERT(DATE,Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) ,12)
,Cast(Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) as DATE)
,TRY_CONVERT(DATE,Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) ,12)
--,TRY_CAST(Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) as DATE) 
,TRY_PARSE(Right( policyexpire ,2)+ Left(Right('0'+policyexpire,6) ,4) as DATE)
from atable


How to fix scientific notation (like 1.23E+004) number data in varchar column that are imported from Excel in SQL Server

When you import data from Excel, sometimes the numeric column (float type) can be implicit converted to scientific notation.
A number 142972 can be saved as 1.42972E+005 as a string, which may cause problem.
But you can convert the data back to its normal numeric format with some T-SQL functions.
Here is a sample code snippet:


create table test (col varchar(15))

insert into test values('1.42972E+007'),('12345678'),('ABCDE12345')
select  ISNULL(Str(Try_CONVERT(float, col)),col) as col 
from test



drop table test