• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 24
  • Last Modified:

Powershell code question Get-Date

Dear Expert

$dateFilter = ((Get-Date).AddMonths(-1).AddDays(-19))
return $dateFilter

Above script shows that current date minus a month and minus 19 days, result is correct, however I would like to add:
-format yyyy-MM-dd somewhere in there so it will show the format correct.

Thanks
0
WeTi
Asked:
WeTi
  • 7
  • 6
1 Solution
 
oBdACommented:
Always keep in mind that PowerShell works with objects. Get-Date will return a DateTime object with all of its nice properties and possibilities. Keep it as such for as long as you can.
Only turn it to its string representation when it's really required - in your case, based on your prior questions, when you pass it to be embedded into the SQL query.
Then you can use the DateTime object's ToString() method:
$dateFilter = (Get-Date).AddMonths(-1).AddDays(-19)
return $dateFilter.ToString('yyyy-MM-dd')

Open in new window

0
 
footechCommented:
Although it's possible to do it all on one line, it just makes it harder to read.  Break it up (one example is below).
$dateFilter = ((Get-Date).AddMonths(-1).AddDays(-19))
$dateFilter.ToString("yyyy-MM-dd")

Open in new window

0
 
WeTiAuthor Commented:
Well I want $dateFilter to only result with the format yyyy-MM-dd not after you reformat, because this variable till do a sqlquery after, now the SQL query is not working when you do that. In database the date format is: yyyy-MM-dd.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
oBdACommented:
$dateFilter = (Get-Date).AddMonths(-1).AddDays(-19).ToString('yyyy-MM-dd')

Open in new window

0
 
WeTiAuthor Commented:
get : The term 'get' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is
 correct and try again.
At line:57 char:27
+         $dateQuery = (get-date (get date $dateFilter).AddMonths( 1 ) -format yyyy-MM-d ...

In SQL query I use this:
      DECLARE
            @RMonth date = '$dateFilter';
Now the sqlQuery shows: the text of (Get-Date).AddMonths(-1).AddDays(-19).ToString('yyyy-MM-dd') now working like this...
0
 
oBdACommented:
Aside from the hyphen missing in the inner "Get-Date" ("get date $dateFilter"), that line is, sorry, a mess.
You've just converted a DateTime object to its string representation in $dateFilter, and now you're using that string representation to get a new DateTime object, add the month you subtracted earlier, and turn it into a string again? Sorry, but that's pretty hard to follow.
That's what I meant with "Keep it as [an object] for as long as you can. Only turn it to its string representation when it's really required"
It would be easier if you posted the currect script you're working on, explaining what it is you want to achieve with these date calculations and formatting.
0
 
WeTiAuthor Commented:
Well the idea is $dateFilter get currentdate and format to yyyy-MM-dd and the date should minus 19 and month will minus 1,
then $sqlQuery will run, and use the $dateFilter result...

$SQLServer = "SQL04"
$SQLDBName = "Prod"
$monthRange = 2
$dateFilter = (Get-Date).AddMonths(-1).AddDays(-19).ToString('yyyy-MM-dd')

function fsqlquery ($dateFilter) {

      $SqlQuery = "
      DECLARE
            @ReminderMonth date = '$dateFilter';
      DECLARE
            @P1 int = (      SELECT      COUNT(*)
                              FROM        Invoice.Reminder R
                              WHERE       R.DatetimeSentToReceiver >= @ReminderMonth
                              AND R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)
                              AND R.ReminderNumber = 1 ),
            @P2 int = (      SELECT      COUNT(*)
                              FROM        Invoice.Reminder R
                              WHERE       R.DatetimeSentToReceiver >= @ReminderMonth
                              AND R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)
                              AND R.ReminderNumber = 2 ),
            @P3 int = (      SELECT      COUNT(*)
                              FROM        Invoice.Reminder R
                              WHERE       R.DatetimeSentToReceiver >= @ReminderMonth
                              AND R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)
                              AND R.ReminderNumber = 3 );
      
      select CONVERT(varchar(7),@ReminderMonth,120) AS 'Month' , @P1 AS 'Reminder 1', @P2 AS 'Reminder 2', @p3 AS 'Reminder 3', SUM(@P1+@P2+@P3) AS 'Reminder total'

"
      
      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;"
      
      $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
      $SqlCmd.CommandText = $SqlQuery
      $SqlCmd.Connection = $SqlConnection
      
      $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
      $SqlAdapter.SelectCommand = $SqlCmd
      
      $DataSet = New-Object System.Data.DataSet
      [void]$SqlAdapter.Fill($DataSet)
      
      $SqlConnection.Close()
      #-----------------------------------------
      
      return $DataSet.Tables[0].Rows
}


$sqlArr = @()

if ($monthRange -gt 1) {
      $monthRange--
      0..$monthRange | % {
            $dateQuery = (get-date (get date $dateFilter).AddMonths( 1 ) -format yyyy-MM-dd )
            if (get-date (get-date $dateQuery) -le (get-date) ) {
                  $sqlArr += fsqlquery $dateQuery
            }
      }
} else {
      $sqlArr += fsqlquery $dateFilter
}
0
 
oBdACommented:
Please post code snippets inside of [code][/code] tags.
At the beginning of the script, you're setting $dateFilter to a [/b]string[/b], subtracting 1 month and 1 day.
At the end of the script, you're starting a loop that's processed two times, each iteration doing exactly the same, and each iteration turning the $dateFilter string (with a date from 1 month earlier) back into a DateTime object, then adding one month again (huh?), and reformatting the object again as string.
What is the intended function of the loop below?
0
 
WeTiAuthor Commented:
For exemple: if 2018-02-20 minus 19days and one month will be 2018-01-01 result.

Then this:
$dateQuery = (get-date (get date $dateFilter).AddMonths( 1 ) -format yyyy-MM-dd )
Will add one month of $dateFilter that will be 2018-02-01 result.

That is why the loop is there.
0
 
oBdACommented:
I can see what it's doing, alas, the deeper meaning somehow still escapes me. That's why I asked "what it is you want to achieve", not "what it is you're doing".
The code inside the "0..$monthRange | % {" loop is executed twice (with the loop variable being 0 and 1), doing exactly the same each time.
What's the point of this loop?
Then you subtract one month at the beginning of the script, only to add it right back inside said loop.
What's the point?
Why not just set the Reminder date to the one you need and run the query with this value immediately? Why the loop and the month calculations that cancel each other out?
0
 
WeTiAuthor Commented:
To automaticly schedule the script, Im going to add a task schedule do run this script on 20th each month, and it will extract the data from database on the 1th date of the month. for exemple: next month will be 20/03 2018, this time it will run the script and it will extract the data from the date 2018-02-01 to 2018-03-01, and loop is doing this: I want extract the 2018-01-01 to 2018-02-01 and 2018-02-01 to 2018-03-01 when I add a -2 value in monthRange, I can also if i want to show 3 months result then add 3 there.
0
 
oBdACommented:
This now sets a reference date to the beginning of the current month.
The loop will then go back the respective amount of months in each iteration.
And the only instance in this script that requires the date in string format is the function, so that's where the ToString() went.
I took the freedom of renaming the function to Get-SqlQuery - functions in PowerShell should follow the naming convention of <Verb>-<Noun>, where <Verb> is from the list retrieved by the cmdlet "Get-Verb".
The SQL query is now in a Here-String.
$SQLServer = "SQL04"
$SQLDBName = "Prod"
$MonthRange = 2
$ReferenceDate = Get-Date -Day 1 -Month ([DateTime]::Now.Month) -Year ([DateTime]::Now.Year) -Hour 0 -Minute 0 -Second 0

Function Get-SqlQuery($ReminderDate) {
	$SqlDateFilter = $ReminderDate.ToString('yyyy-MM-dd')
	$SqlQuery = @"
		DECLARE 
			@ReminderMonth date = '$($SqlDateFilter)';
		DECLARE
			@P1 int = (	SELECT      COUNT(*)
						FROM        Invoice.Reminder R
						WHERE       R.DatetimeSentToReceiver >= @ReminderMonth
						AND			R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)
						AND			R.ReminderNumber = 1 ),
			@P2 int = (	SELECT      COUNT(*)
						FROM        Invoice.Reminder R
						WHERE       R.DatetimeSentToReceiver >= @ReminderMonth
						AND			R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)
						AND			R.ReminderNumber = 2 ),
			@P3 int = (	SELECT      COUNT(*)
						FROM        Invoice.Reminder R
						WHERE       R.DatetimeSentToReceiver >= @ReminderMonth
						AND			R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)
						AND			R.ReminderNumber = 3 );
		SELECT
			CONVERT(varchar(7), @ReminderMonth,120) AS 'Month',
			@P1 AS 'Reminder 1',
			@P2 AS 'Reminder 2',
			@p3 AS 'Reminder 3',
			SUM(@P1+@P2+@P3) AS 'Reminder total'
"@

	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;"

	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $SqlQuery
	$SqlCmd.Connection = $SqlConnection

	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlAdapter.SelectCommand = $SqlCmd
       
	$DataSet = New-Object System.Data.DataSet
	[void]$SqlAdapter.Fill($DataSet)
       
	$SqlConnection.Close()
	#-----------------------------------------
	return $DataSet.Tables[0].Rows
}

$SqlArr = @()
For ($MonthsBack = 1; $MonthsBack -le $MonthRange; $MonthsBack++) {
	$SqlArr += Get-SqlQuery -ReminderDate $ReferenceDate.AddMonths(-$MonthsBack)
}
$SqlArr

Open in new window

0
 
WeTiAuthor Commented:
Lovely solution, you basicly created a ReferenceDate and format the date in it... lovely! But I don't understand this:
Function Get-SqlQuery($ReminderDate) {
      $SqlDateFilter = $ReminderDate.ToString('yyyy-MM-dd')
The $ReminderDate has been called in the for loop below...  If you got time please explain the For loop below.
Thanks for the wonderful support.
0
 
oBdACommented:
$ReferenceDate is not formatted in any way. It's a DateTime object, with all its methods and properties, that holds the beginning of the current month.
That's why inside the loop, the AddMonths() method can be executed directly when calling the function: $ReferenceDate.AddMonths(-$MonthsBack)
$ReminderDate is a local argument of the Get-SqlQuery function.
The main loop calculates the required date for each month it goes back, and passes it to the function - still as a DateTime object.
Only the function (the SQL script, actually) requires the date in a certain string representation - that's what $SqlDateFilter = $ReminderDate.ToString('yyyy-MM-dd') does.
$ReminderDate is still a full DateTime object, $SqlDateFilter is a simple string.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now