changing format of TIMESTAMP retrieved from sql database table

PowerShell Help
1 
 function Invoke-SQL {
        param(
                #name of SQL instance (server)
                [string] $dataSource = "MSSQLserver\instance",
                #sql database name
                [string] $database = "DBname",
                #what you are selecting from the database 
                [string] $sqlCommand = $("SELECT timestamp FROM [DBname].[dbo].[tablename]")
             )

                        $connectionString = "Data Source=$dataSource; " + "Integrated Security=SSPI; " + 

                        "Initial Catalog=$database"
                            #object to pass the connection string used to connect to the sql server instance
                            $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
                            #create your command object
                            $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
                            #open sql server instance
                            $connection.Open()

                        #create your data adapter object
                        $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
                        #create your data set and fill it
                        $dataset = New-Object System.Data.DataSet
                        write-output $adapter.Fill($dataSet) | Out-Null

                        #close sql server instance
                        $connection.Close()
                        #retrieve your data
                        $dataSet.Tables
                        }

    $FeedID = Invoke-SQL
    $FeedID

Currently the results are returning in the format 2017-06-19 22:41:34.717 but I need to convert this to 2017-06-19 22:41:34 only. I need to remove .717 from the results.

I’m think I need to alter line 7

[string] $sqlCommand = $("SELECT timestamp FROM [DBname].[dbo].[tablename]")
2

The quick and dirty way is to split on the ‘.’ and select the first element:

('2017-06-19 22:41:34.717').split('.')[0]

However, if you’re on MS SQL 2008 or higher you should be able to CAST to smalldatetime. I don’t have a SQL server to test my syntax but it’s something like:

[string] $sqlCommand = $("SELECT CAST(timestamp AS smalldatetime) FROM [DBname].[dbo].[tablename]")
3

Or convert it to datetime and format?

'{0:yyyy-MM-dd HH:mm:ss}' -f  [datetime]'2017-06-19 22:41:34.717'

2017-06-19 22:41:34

            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              4
              
            

            

              
Thank you both for your tips. I will try


[string] $sqlCommand = $("SELECT CAST(timestamp AS smalldatetime) FROM [DBname].[dbo].[tablename]")



and I will post the outcome soon

            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              5
              
            

            

              
[string] $sqlCommand = $("SELECT CAST(timestamp AS smalldatetime) FROM [DBname].[dbo].[tablename]")


resulted in the same results as


[string] $sqlCommand = $("SELECT timestamp FROM [DBname].[dbo].[tablename]")


with the format as 2017-06-19 22:41:34.717


Is this how you’d split with the ‘.’


function Get-Server1date {
                        param(
                        [string] $sqlCommand = $("SELECT CAST(timestamp) FROM [DBname].[dbo].[tablename]")
                        )

                        $connectionString = "Data Source=$dataSource; " + "Integrated Security=SSPI; " + 

                        "Initial Catalog=$database"
                        $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
                        $command = new-object system.data.sqlclient.sqlcommand($sqlCommand.Split('.')[0],$connection)
                        $connection.Open()

                        $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
                        $dataset = New-Object System.Data.DataSet
                        write-output $adapter.Fill($dataSet) | Out-Null

                        $connection.Close()
                        $dataSet.Tables
                        }

                        $FeedID = Invoke-SQL
                        $FeedID


?

Thanks for all your help