Date Time format issue in powershell

Tagged: 

This topic contains 7 replies, has 2 voices, and was last updated by  Robin16 3 years, 9 months ago.

  • Author
    Posts
  • #10989

    Robin16
    Participant

    Hi,

    I have a requirement to write a script using powershell. Logic here is

    1. $PointinTime has a value of 15:30:00 ( we get this time value from a sql server table, please see the below script )
    2. check if the current time (in the following format 13:23:10 ) is less than 15:30:00 ( step 1 ), if the time is less than step 1 then continue the script with yesterdays date and step 1 time ( 15:30:00 )
    3. if not proceed with today's date ( format : 2013-10-22 ) and step 1 time ( 15:30:00 )

    We are restoring the sql server database to a point in time restore.

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet
    $SqlConnection.ConnectionString = "Server = DBATest; Database = master; Integrated Security = True"
    $SqlCmd.CommandText = " use Test
    select timevalue from testtable where jobname = 'DB_refresh'"
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlAdapter.Fill($DataSet)|out-null
    $rownumber_ = $Dataset.Tables[0].Rows.Count
    if($number -eq 1)
    {
    $PointinTime = $($Dataset.Tables[0].Rows[0][0])
    $PointinTime # it has a time value of 15:30:00
    }
    $currenttime = get-date -format T ( output of this is 1:23:09 PM , but i'm looking for a 24 hrs format with no PM or AM )

    Can anyone please help me on formating the date time ?

    Thanks in advance.

  • #10990

    Rob Campbell
    Participant

    (get-date).tostring('HH:mm:ss')

    12:56:47

  • #10991

    Robin16
    Participant

    Thanks Rob for the quick response. I was able to get the current date and yesterdays date syntax ( in format 2013-10-22)

    Can you also help me in concatenating these datetimes and please let me know where i was going wrong.

    for example

    $PointinTime = '15:30:00'
    $currenttime = (get-date).tostring('HH:mm:ss')
    $currentdate = get-date -format "yyyy-M-d"
    $Yestdate = "{0:yyyy-M-d}" -f (get-date).AddDays(-1)

    If ( $currenttime -lt $PointinTime)
    {
    $Time = '$Yestdate' + " + '$PointinTime'
    }
    Else
    {
    $Time = '$currentdate' + " + '$PointinTime'
    }

    When i execute this i dont see any output.

    Thanks in advance

  • #10992

    Rob Campbell
    Participant

    I think this might be closer to what you want:

    if ((get-date) -lt [datetime]'15:30:00')
    {
    $Time = '{0} 15:30:00' -f (get-date).tostring('yyyy-M-d')
    }
    else {
    $Time = '{0} 15:30:00' -f (get-date).adddays(-1).tostring('yyyy-M-d')
    }

  • #10993

    Robin16
    Participant

    Thanks again Rob. Here $PointinTime is dynamic time which we generate from sql server table, we cannot hard code the time in script.

    Actually the script is working....

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet
    $SqlConnection.ConnectionString = “Server = DBATest; Database = master; Integrated Security = True”
    $SqlCmd.CommandText = ” use Test
    select timevalue from testtable where jobname = ‘DB_refresh’”
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlAdapter.Fill($DataSet)|out-null
    $rownumber_ = $Dataset.Tables[0].Rows.Count
    if($number -eq 1)
    {
    $PointinTime = $($Dataset.Tables[0].Rows[0][0])
    $PointinTime # time value is 15:30:00
    }
    $currenttime = (get-date).tostring('HH:mm:ss')
    $currentdate = get-date -format "yyyy-M-d"
    $Yestdate = "{0:yyyy-M-d}" -f (get-date).AddDays(-1)

    If ( $currenttime -lt [datetime]$PointinTime)
    {
    $Time = $Yestdate + " " + $PointinTime
    }
    else
    {
    $Time = $currentdate + " " + $PointinTime
    }

    $Time

    Output is 2013-10-22 15:30:00 ( which is in the expected format ) but logic is missing here as current time is less than pointintime 15:30:00.
    It's supposed to give 2013-10-21 15:30:00 ( previous day date ) but the output is today's date.

    Is there any thing i'm missing here ?

    Thanks for your help.

  • #10994

    Rob Campbell
    Participant

    You can variablize the PointInTime by introducing the variable into the format string, and switching to double quotes so it gets expanded:

    $PointInTime = '15:30:00'

    if ((get-date) -lt [datetime]'15:30:00')
    {
    $Time = "{0} $PointInTime" -f (get-date).tostring('yyyy-M-d')
    }
    else {
    $Time = "{0} $PointInTime" -f (get-date).adddays(-1).tostring('yyyy-M-d')
    }

  • #11000

    Robin16
    Participant

    Thanks Rob. But i'm still facing the same issue as i have mentioned in my above thread.

  • #11007

    Robin16
    Participant

    Its working now...thanks a lot for your help Rob. Appreciate it.

You must be logged in to reply to this topic.