changing format of TIMESTAMP retrieved from sql database table

This topic contains 4 replies, has 3 voices, and was last updated by  David Mower 3 months, 2 weeks ago.

  • Author
    Posts
  • #75359

    David Mower
    Participant
     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]")
    
  • #75365

    Matt Bloomfield
    Participant

    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]")
  • #75476

    js
    Participant

    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
    
    
    		
    	
  • #75482

    David Mower
    Participant

    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

  • #77049

    David Mower
    Participant
    [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

You must be logged in to reply to this topic.