Cast datetime as date still shows time

Question

I'm trying to cast my datetime as date to get rid of the time, but the query results still show the time. When I compare with what I see online, it looks correct.

How do I fix it?

..
, CAST("Process"."StartDate" AS date)
..

The result still shows like this:

4/14/2019 0:00

Update: I'm trying to use convert, as suggested below, but I'm getting Exception calling "fill" with "1" arguments.

There's probably something wrong with my syntax for the convert. Any ideas? All the online examples I see use datetime with convert. The complete query in powershell looks like this:

$SQLquery_Proc = @"
SELECT DISTINCT 
   Process.Process_ID
   ,"Process"."ProcessName"
   ,ProcessFacilities = STUFF(
                 (SELECT ',' + apf.FacCode FROM ProcessFacilities apf where apf.Process_ID = "Process"."Process_ID"  FOR XML PATH ('')), 1, 1, ''
               ) 
   , "People"."Last_Name"
   , "People"."First_Name"
   , "People"."Middle_Initial"
   , "People"."Degree"
   , "Process"."P_ID"
   , "People_Facilities_ALL"."FacCode" as "People_FacCode"
   , "People_Facilities_ALL"."Current_status"
   , "People_Facilities_ALL"."Status_category"
   , convert("People_Facilities_ALL"."Status_from_date", 101)  --issue?
   , convert("People_Facilities_ALL"."Next_r_date", 101)        --issue?
FROM   
   Process 
   left JOIN "DB"."dbo"."People" ON "Process"."PRACT_ID"="People"."P_ID" 
   left JOIN "DB"."dbo"."People_Facilities_ALL" ON "Process"."P_ID"="People_Facilities_ALL"."PRACT_ID" 
   ORDER BY 
   "People"."P_ID"
"@

Update2: I figured out how to get convert to work. Thanks for the help!

, convert(VARCHAR(23),"People_Facilities_ALL"."Status_from_date", 101)

Which dbms are you using? (When it comes to date/time, many products are far from ANSI SQL compliant.) — jarlh, Jun 17 '19 at 12:52
It's a sql query inside a powershell script run through task manager, the results are written to an excel file. — Michele, Jun 17 '19 at 12:55
@Michele: Nice to know but that doesn't answer the question which DBMS is used. — sticky bit, Jun 17 '19 at 12:56
Do you mean SQL? What is an example of a DBMS? I'm sorry, I'm relatively new to certain terms. — Michele, Jun 17 '19 at 12:58
There is no dedicated date type in .NET, and hence there isn't one in PowerShell. It'll still end up as a `DateTime`. If you want to show only the date, use an appropriate format string (i.e. `.ToString("yyyy-MM-dd")`). — Jeroen Mostert, Jun 17 '19 at 13:01
*"Do you mean SQL? What is an example of a DBMS? I'm sorry, I'm relatively new to certain terms. "* @Michele SQL is just a standard from ANSI/ISO which defines the statements, Database Management System (DBMS) or also called Relational Database Management System (RDBMS). are the products based on that standard.. Some product examples are SQL Server (MSSQL), MySQL, PostgreSQL, Oracle database or SQLite — Raymond Nijland, Jun 17 '19 at 13:01
Have a look at [Date and Time Conversions Using SQL Server](https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/) — Theo, Jun 17 '19 at 13:05
This is, as Jeroen Mostert wrote, a PowerShell issue. Your Sql Server query returns a `Date` data type, which is mapped, due to the lack of a better option, to the .Net DateTime struct. Since the .Net framework does not include a Date only type — Zohar Peled, Jun 17 '19 at 13:07
Michele if you can't figure it out or make it work with the suggestions. most likely it would help if you share some example data from your table and the PowerShell code itself... — Raymond Nijland, Jun 17 '19 at 13:11
If modifying the code that reads the result is too complicated or too cumbersome, it is possible to have SQL Server output an appropriate value with `CONVERT` (e.g. `CONVERT(CHAR(10), [Process].[StartDate], 120)`. In general, this doesn't belong in a query and formatting should be left to the client code, but if this query is used only to produce results for PowerShell to write directly to a file it's halfway acceptable. Note that the type changes to a `string` if you do this, which might not end up correctly in the Excel file. Check how (typed) values get converted from PowerShell to Excel. — Jeroen Mostert, Jun 17 '19 at 13:14
I'm trying the convert, that I saw in Theo's link, and Jeroen is discussing above. I'll keep you posted. I don't handle the query results (data result strings or rows) in my Powershell script, and I'm not using the .net plugin with powershell, so I'd rather not add that. — Michele, Jun 17 '19 at 13:15
I added an update above. There must be an issue with my convert that I added to the query. — Michele, Jun 17 '19 at 13:48
I added Update2, which shows how I got convert to work. Thanks for the help!! :) — Michele, Jun 17 '19 at 14:03

Cast datetime as date still shows time

0 Answers0