Date Time format issue in powershell

Tagged: 

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

  • Author
    Posts
  • #10989
    Profile photo of Robin16
    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
    Profile photo of Rob Campbell
    Rob Campbell
    Participant

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

    12:56:47

  • #10991
    Profile photo of Robin16
    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
    Profile photo of Rob Campbell
    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
    Profile photo of Robin16
    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
    Profile photo of Rob Campbell
    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
    Profile photo of Robin16
    Robin16
    Participant

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

  • #11007
    Profile photo of Robin16
    Robin16
    Participant

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

You must be logged in to reply to this topic.