changing format of TIMESTAMP retrieved from sql database table

Welcome Forums General PowerShell Q&A changing format of TIMESTAMP retrieved from sql database table

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
1 year, 9 months ago.

  • Author
    Posts
  • #75359

    Participant
    Topics: 3
    Replies: 5
    Points: 1
    Rank: Member
     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

    Participant
    Topics: 1
    Replies: 284
    Points: 41
    Rank: Member

    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
    Topics: 21
    Replies: 586
    Points: 1,122
    Helping Hand
    Rank: Community Hero

    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

    Participant
    Topics: 3
    Replies: 5
    Points: 1
    Rank: Member

    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

    Participant
    Topics: 3
    Replies: 5
    Points: 1
    Rank: Member
    [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

The topic ‘changing format of TIMESTAMP retrieved from sql database table’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort