Invoke-DbaQuery on Date column returns : <date> 00:00:00

[MikeyBronowski]

I have a [date] type column and in SSMS the results I get look like this
select [date] from table;

Invoke-DbaQuery -SqlInstance $instance -Database tempdb -Query 'SELECT [date] FROM table;'
gives me

date               
----               
02/02/2020 00:00:00
20/02/2020 00:00:00

Is that expected? How can I make it to return just the date without all the zeros?

[niphlod]

We don't mangle any type of the returned object afaik. Are those dates or datetimes? Gettype() is your best friend here Il mer 24 feb 2021, 21:57 Mikey Bronowski <[email protected]> ha scritto:

…

I have a [date] type column and in SSMS the results I get look like this select [date] from table; [image: image] <https://user-images.githubusercontent.com/13431760/109064465-5b37c480-76e2-11eb-9418-1003790294e7.png> Invoke-DbaQuery -SqlInstance $instance -Database tempdb -Query 'SELECT [date] FROM table;' gives me date ---- 02/02/2020 00:00:00 20/02/2020 00:00:00 Is that expected? How can I make it to return just the date without all the zeros? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#7169>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAA52B5RXEGMFIZZJHBVEKLTAVR5FANCNFSM4YFHT5NQ> .

[MikeyBronowski]

In SQL it is date
CREATE TABLE table ([date] date);
in PowerShell looks like it's datetime - unless I am doing something wrong here

$SQLresults[0][0].GetType()
IsPublic IsSerial Name                                     BaseType                                                                                                                                            
-------- -------- ----                                     --------                                                                                                                                            
True     True     DateTime                                 System.ValueType  

$SQLresults[0][0]
02 February 2020 00:00:00

[ghost]

I have a [date] type column and in SSMS the results I get look like this
select [date] from table;

Invoke-DbaQuery -SqlInstance $instance -Database tempdb -Query 'SELECT [date] FROM table;'
gives me

date               
----               
02/02/2020 00:00:00
20/02/2020 00:00:00

Is that expected? How can I make it to return just the date without all the zeros?

Hi

I am having the exact same issue , did you find a work around for this this ?? .

I have seen some references where this is related to powershell using .Net libraries , if I use invoke-sqlcmd I get the same result with the date column returned to powershell with a time stamp .
https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

[niphlod]

whoppsie, forgot about this one. .net doesn't have a "Date" type, so what you get back is a datetime (with the time part truncated with zeroes). Simply put, there's no way to get a date back: if you want to see only the date part you can either format as a string in the query or format as a string the cell before displaying it.
Try this, same deal.

$a = get-date
$b = $a.date

$b is still a DateTime.

[MikeyBronowski]

Thank you for the explanation. I am sure it will help others to understand the issue.

[ghost]

Thanks makes sense , i ended up using sqlcmd its nasty but it does what I need and preserves the date in the correct format.

I had to dump the table out to a file , im sure theres a million other ways to do this.

$Command = 'sqlcmd -s, -W -Q "set nocount on; select * from [databasename].[dbo].[' + $TabletoExport + ']" | findstr /v /c:"-" /b > ' + "c:\temp\$TabletoExport.csv" $Command | Out-File c:\temp\dumptable.cmd -Encoding ascii -Force Start-Process C:\temp\dumptable.cmd