Author Posts

October 22, 2013 at 9:26 am

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.

October 22, 2013 at 9:57 am

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

12:56:47

October 22, 2013 at 11:02 am

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

October 22, 2013 at 11:22 am

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')
}

October 22, 2013 at 11:57 am

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.

October 22, 2013 at 12:38 pm

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')
}

October 23, 2013 at 5:51 am

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

October 23, 2013 at 7:29 am

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