none
Powershell DateTime to String RRS feed

  • Question

  • Hi,

    I'm currently getting data from a SQL Server. I use System.Data.DataSet to return data as well.

    In the data, there is a column in DateTime format.

    I would like to know how can I convert a DateTime column in my array to a String.

    I have found this code, but I don't want to enumerate all the fields:

    $ds.tables[0] | select @{n='CreatedTime';e={$_.CreatedTime.ToString("MM/dd/yyyy")}}
    Thanks


    • Edited by Bilis518 Tuesday, May 27, 2014 3:20 PM titile error
    Tuesday, May 27, 2014 3:18 PM

Answers

  • Answer code :

    $csvPath    = "C:\Users\test\Desktop\Export.csv"
    
    ##### FUNCTIONS #####
    
    function Run-SQLQuery($SqlQuery){
    
        [string]$SqlServer = "MSSQLsrv\Instance"
        [string]$SqlDBName = "MyDatabase"
        [string]$SqlUser   = "test"
        [string]$SqlPass   = "test"
        [int]$Timeout      = 0
            
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = False; User ID = $SqlUser; Password = $SqlPass;"
    
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlCmd.CommandTimeout = $Timeout
    
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd 
    
        $DataSet = New-Object System.Data.DataSet
        $SqlAdapter.Fill($DataSet) | out-null
        $SqlConnection.Close() 
        
        return $dataSet.Tables[0]
        
        }
    
    ##### MAIN SCRIPT #####
    
    
    # Requete SQL
    [string]$QuerySEP = "SELECT * FROM SEG_FACTUREVERSITESOFT 
                        WHERE (D_RAPPRO >= (DATEADD([day], -5, GETDATE()))
                        OR  D_PAIEMENT >= (DATEADD([day], -30, GETDATE())))
                        AND CLIENTID > 400"
    
    [string]$QueryGrp = "SELECT * FROM SEG_FACTUREVERSITESOFT 
                        WHERE (D_RAPPRO >= (DATEADD([day], -5, GETDATE()))
                        OR  D_PAIEMENT >= (DATEADD([day], -5, GETDATE())))
                        AND CLIENTID <= 400"
    
    # Execution des requetes
    $DataSEP = Run-SQLQuery $QuerySEP
    $DataGrp = Run-SQLQuery $QueryGrp
    
    # Concatenation des resultats en une seule table
    $Data = $DataSEP + $DataGrp
    
    # Traitement de certaines données
    ## Conversion des champs dates en string
    ## Conversion du champs NETAMOUNT en decimal arrondi
    $Data = $Data | Select  REFARCHIVE,
                            @{n="D_RAPPRO"  ;e={$_.D_RAPPRO.ToString("ddMMyyyy")}},
                            @{n="D_PAIEMENT";e={$_.D_PAIEMENT.ToString("ddMMyyyy")}},
                            VENDORIDX,
                            VENDOR,
                            CPTANUM,
                            CLIENTID,
                            FACTORID,
                            FACTOR,
                            DOCUMENT,
                            @{n="NETAMOUNT";e={[decimal]::round($_.NETAMOUNT,2)}},
                            TOTALAMOUNT,
                            @{n="INVOICEDATE";e={$_.INVOICEDATE.ToString("ddMMyyyy")}},
                            INVOICENUM,
                            REFPAYM,
                            ACTIVITE,
                            CHANTIER
    
    # Export des données en csv
    $Data | Export-Csv -Path $csvPath -NoTypeInformation -Force -Delimiter ";"
    
    # Suppression des double quote
    (Get-Content -Path $csvPath) -replace "`"","" | Set-Content $csvPath
    

    • Marked as answer by Bilis518 Friday, May 30, 2014 7:56 AM
    Friday, May 30, 2014 7:55 AM

All replies

  • Why is that not working?  Are you sure the field is a datatime and not a string already?

    Are you a student?  We have no idea how you are getting this dataset.  Are you hand generating it?


    ¯\_(ツ)_/¯


    • Edited by jrv Tuesday, May 27, 2014 4:19 PM
    Tuesday, May 27, 2014 4:18 PM
  • The Query Works fine.

    But a dataset Field is a datetime.

    I would like to convert This Field to string but i dont know how...

    Can i convert the Field?

    Can i add a duplicate column?

    Can i copy data to annoter dataset?

    Thanks

    Tuesday, May 27, 2014 5:21 PM
  • How do you know it is a datetime when retrieved.  How did you create the dataset?

    What happens when you do this:

    ($ds.tables[0] |{$_.CreatedTime} )[0].GetType()


    ¯\_(ツ)_/¯

    Tuesday, May 27, 2014 5:37 PM
  • I create the dataset with a function witch query a SQL Server :

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = $Timeout
    
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd 
    
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet) | out-null
    $SqlConnection.Close() 
        
    return $dataSet.Tables[0]

    This are the properties of the dataset :

    $dataset |gm 

       TypeName: System.Data.DataRow

    Name              MemberType            Definition                                
    ----              ----------            ----------                                
    AcceptChanges     Method                System.Void AcceptChanges()               
    BeginEdit         Method                System.Void BeginEdit()                   
    .....
    ...
    CLIENTID          Property              System.String CLIENTID {get;set;}         
    CPTANUM          Property              System.String CPTANUM {get;set;}          
    DOCUMENT       Property              System.String DOCUMENT {get;set;}         
    D_PAIEMENT     Property              System.DateTime D_PAIEMENT {get;set;}     
    D_RAPPRO        Property              System.DateTime D_RAPPRO {get;set;} 


    • Edited by Bilis518 Wednesday, May 28, 2014 7:33 AM more explain
    Wednesday, May 28, 2014 7:20 AM
  • I do not see this field in your schema: "CreatedTime"

    You cannot convert a field that does not exist.

    Example:

    [datetime]::Today.ToString('yyyy-MM-dd')


    ¯\_(ツ)_/¯

    Wednesday, May 28, 2014 1:06 PM
  • ... CreatedTime was a sample i found on the internet...

    You can see in the $dataset | gm results some fields with System.DateTime property

    First I 've tried this code in a ForEach loop in the $dataset :

    $_.D_RAPPRO   = $_.D_RAPPRO.ToString("ddMMyyyy")

    but the error was : The string is not recognize as a valide DateTime.

    This erros is logic, you can not write a string in a field fomated as a DateTime.

    I don't know if there is another solution instead of forcing the data property of a column... create another dataset...???

    Thanks for any idea

    Wednesday, May 28, 2014 1:39 PM
  • I think you need to learn PowerShell first.  Your idea of how to do this is a bit off.

    You cannot convert a datatime field to a string.  You can only output it as a string.  Once the field is defined as a datetime it will always be a datetime.

    Why do you think you need to do this?


    ¯\_(ツ)_/¯

    Wednesday, May 28, 2014 1:53 PM
  • I need to export some data of a SQL server.

    Then I need to change some data to export as a csv.

    For fields containing date, the csv should be like 'ddMMyyyy'

    What are your suggestions ?

    Wednesday, May 28, 2014 2:01 PM
  • Have you tried to export the data?  How did you try to do that?

    $dataSet.Tables[0] | Export-Csv mycsv.csv -NoType


    ¯\_(ツ)_/¯

    Wednesday, May 28, 2014 2:04 PM
  • Start here:

    HELP Export-Csv -FULL


    ¯\_(ツ)_/¯

    Wednesday, May 28, 2014 2:07 PM
  • Before exporting data i need to change the field containing date from

    27/04/2014 00:00:00 to

    27042014

    Wednesday, May 28, 2014 2:53 PM
  • Before exporting data i need to change the field containing date from

    27/04/2014 00:00:00 to

    27042014

    But that is what you did in your first post.

    See: HELP Select-String -FULL


    ¯\_(ツ)_/¯

    Wednesday, May 28, 2014 2:59 PM
  • Wednesday, May 28, 2014 3:01 PM
  • That's I written in my first post...

    No other way ?

    Friday, May 30, 2014 6:44 AM
  • That's I written in my first post...

    No other way ?


    What is wrong with this way?

    ¯\_(ツ)_/¯

    Friday, May 30, 2014 7:51 AM
  • Answer code :

    $csvPath    = "C:\Users\test\Desktop\Export.csv"
    
    ##### FUNCTIONS #####
    
    function Run-SQLQuery($SqlQuery){
    
        [string]$SqlServer = "MSSQLsrv\Instance"
        [string]$SqlDBName = "MyDatabase"
        [string]$SqlUser   = "test"
        [string]$SqlPass   = "test"
        [int]$Timeout      = 0
            
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = False; User ID = $SqlUser; Password = $SqlPass;"
    
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlCmd.CommandTimeout = $Timeout
    
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd 
    
        $DataSet = New-Object System.Data.DataSet
        $SqlAdapter.Fill($DataSet) | out-null
        $SqlConnection.Close() 
        
        return $dataSet.Tables[0]
        
        }
    
    ##### MAIN SCRIPT #####
    
    
    # Requete SQL
    [string]$QuerySEP = "SELECT * FROM SEG_FACTUREVERSITESOFT 
                        WHERE (D_RAPPRO >= (DATEADD([day], -5, GETDATE()))
                        OR  D_PAIEMENT >= (DATEADD([day], -30, GETDATE())))
                        AND CLIENTID > 400"
    
    [string]$QueryGrp = "SELECT * FROM SEG_FACTUREVERSITESOFT 
                        WHERE (D_RAPPRO >= (DATEADD([day], -5, GETDATE()))
                        OR  D_PAIEMENT >= (DATEADD([day], -5, GETDATE())))
                        AND CLIENTID <= 400"
    
    # Execution des requetes
    $DataSEP = Run-SQLQuery $QuerySEP
    $DataGrp = Run-SQLQuery $QueryGrp
    
    # Concatenation des resultats en une seule table
    $Data = $DataSEP + $DataGrp
    
    # Traitement de certaines données
    ## Conversion des champs dates en string
    ## Conversion du champs NETAMOUNT en decimal arrondi
    $Data = $Data | Select  REFARCHIVE,
                            @{n="D_RAPPRO"  ;e={$_.D_RAPPRO.ToString("ddMMyyyy")}},
                            @{n="D_PAIEMENT";e={$_.D_PAIEMENT.ToString("ddMMyyyy")}},
                            VENDORIDX,
                            VENDOR,
                            CPTANUM,
                            CLIENTID,
                            FACTORID,
                            FACTOR,
                            DOCUMENT,
                            @{n="NETAMOUNT";e={[decimal]::round($_.NETAMOUNT,2)}},
                            TOTALAMOUNT,
                            @{n="INVOICEDATE";e={$_.INVOICEDATE.ToString("ddMMyyyy")}},
                            INVOICENUM,
                            REFPAYM,
                            ACTIVITE,
                            CHANTIER
    
    # Export des données en csv
    $Data | Export-Csv -Path $csvPath -NoTypeInformation -Force -Delimiter ";"
    
    # Suppression des double quote
    (Get-Content -Path $csvPath) -replace "`"","" | Set-Content $csvPath
    

    • Marked as answer by Bilis518 Friday, May 30, 2014 7:56 AM
    Friday, May 30, 2014 7:55 AM
  • Another drive-by bit of nonsense. 

    Wow!  We already guessed that last month. What took you so long?


    ¯\_(ツ)_/¯

    Friday, May 30, 2014 8:01 AM
  • If you read my first post again , I said I would not enumerate all the fields...

    Thats why I post on this forum to know if there was another better option.

    Reading your replies I understand there is no other way.

    Thank you anyway


    • Edited by Bilis518 Friday, May 30, 2014 8:20 AM correctoin
    Friday, May 30, 2014 8:19 AM
  • I don't think we can understand what you are saying or what your post is about.

    Why did you just post all of that scrip which seems to have nothing to do with your question?  You asked how to convert a date to a string.  That is how.  The rest of your last post has nothing to do with your question or its answer.  Marking it as an answer is no help to anyone.

    What was the purpose of your original question?


    ¯\_(ツ)_/¯

    Friday, May 30, 2014 8:28 AM
  • I need to convert a date time format as string.

    In a dataset I can't because the column is formatted as datetime and don't accept string. So I can't convert directly in the dataset.

    My question was, how can I convert some data in a DateTime format as string, not using something like this :

    $ds.tables[0] | select @{n='CreatedTime';e={$_.CreatedTime.ToString("MM/dd/yyyy")}}

    because this solution oblige me to enumerate all the fileds I need (see answer).

    Do you understand my question ?

    Friday, May 30, 2014 9:31 AM
  • You are not storing in the dataset you are outputting.  If you have a dataset and you want to assign a column or parameter that is defined as datetime it will take a datetime object. All date, datetime, shortdate and other date types in databases can be assigned in ADO.Net using datetime objects.  ADO.Net knows how to do the conversion.

    If you are storing in a CSV or text file then you may need to convert it as the native conversion will be long date format in the culture you are set for.

    This is a string. That is what "ToString" does,

    $ds.tables[0] | select @{n='CreatedTime';e={$_.CreatedTime.ToString("MM/dd/yyyy")}}

    It is identical to your use:

    @{n="D_RAPPRO"  ;e={$_.D_RAPPRO.ToString("ddMMyyyy")}}

    I do not understand what you are asking.  It makes no sense.  THe answer to the exact question is to use "ToString" to convert a date into a custom string.

    Run this:
    [datetime]0|gm |?{$_.Name -like 'To*'}

    And this:
    ([datetime]0).ToString


    ¯\_(ツ)_/¯

    Friday, May 30, 2014 11:30 AM
  • Try this:

    #Example FixDate  (search and replace DATEFIELD for FIELDNAME you are fixing. --DJW
    #$Patients.Columns.Add("DATEFIELD2","System.String").setOrdinal($Patients.Columns.IndexOf("DATEFIELD"))
    #$Patients |  where-object {$_.DATEFIELD -is [datetime]} | % { $_.DATEFIELD2=$_.DATEFIELD.ToString("MM/dd/yyyy") }
    #$Patients.Columns.Remove("DATEFIELD")
    #$Patients.Columns["DATEFIELD2"].ColumnName="DATEFIELD"


    • Edited by Dave'W Friday, October 19, 2018 5:17 PM
    • Proposed as answer by Dave'W Friday, October 19, 2018 5:17 PM
    Friday, October 19, 2018 4:53 PM