0

I am creating a report for work, in which there is a certain assignment date. My work develops and maintains a program that works with these dates and displays DATETIME as a dropdown in the frontend. I will include pictures later.

My solution for the problem now is to have two separate columns for the date and for the time of that assignment, however preferably I want to do it in one.

This is my current code:

DECLARE @Column_3 INT = 1061

SELECT 
    (SELECT TOP 1 t1.Column_1 FROM Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY t1.Column_4 DESC)  AS [Rand ID]
    , CASE  
        WHEN t2.Column_5 = '' then 'Not Used'
        ELSE t2.Column_5
        END AS [Strata ID]
    , 'Not Used' AS [Description]
    , t2.Column_6 AS [Treatment]
    , CASE t2.Column_6 
        WHEN 1 THEN 'Active'
        WHEN 2 THEN 'Placebo'
        END AS [Description]
    , CONVERT(DATETIME, (SELECT TOP 1 t1.Column_4 FROM Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY Column_4 DESC), 1) AS [Assignment Date]
    , RIGHT(CONVERT(NVARCHAR(24), (SELECT TOP 1 t1.Column_4 from Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY Column_4 DESC), 13), 12) AS [Assignment Time]
    , t5.Column_9 AS [Institute ID]
    , t3.Column_10 AS [Subject ID]


FROM Table_2 t2 
 LEFT OUTER JOIN Table_3 t3 ON 
    t2.Column_2=t3.Column_2 AND 
    t2.Column_3=t3.Column_3
 LEFT OUTER JOIN Table_4 t4 ON
    t4.Column_3=t3.Column_3 AND 
    t4.Column_7=t3.Column_7
 LEFT OUTER JOIN Table_5 t5 ON 
    t5.Column_8 = t4.Column_8 AND 
    t5.Column_3 = t4.Column_3

WHERE t2.Column_3=@Column_3 AND t2.Column_2 IS NOT NULL
ORDER BY t2.Column_9 ASC

My question is primarily about the Assignment Date and Assignment Time.

, CONVERT(DATETIME, (SELECT TOP 1 t1.Column_4 FROM Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY Column_4 DESC), 1) AS [Assignment Date]
, RIGHT(CONVERT(NVARCHAR(24), (SELECT TOP 1 t1.Column_4 from Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY Column_4 DESC), 13), 12) AS [Assignment Time]

As you can see, I use two columns now. But if I concatenate the two columns, the Assignment Date changed formats completely. Besides that, in the frontend of the program it does not show up as a datepicker.

Can I concatenate the two columns without losing the DATETIME datetype, functionalities + information displayed in the column?

This is what the results look like:

Display of the result in Microsoft SQL.

Display of the result in the front end.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Juacquesch
  • 1
  • 1
  • 2
    ___I'm working in Microsoft SQL Server___ Then WHY have you tagged `MYSQL` and not tagged `SQLServer`? tags are designed to attract the correct audience for your question, incorrect tags just gather an angry mod :) – RiggsFolly Feb 25 '22 at 11:41
  • Why not use `DATEADD` instead? You can't concatenate date and time data types. – Thom A Feb 25 '22 at 11:53
  • You confuse two different but related things. How YOU choose to represent your data in your application has little to do with a query that retrieves data. So is your question really about just the query? It seems to be both about how you formulate a resultset, represent it in your application, and then (presumably) update the database when the user has completed editing. Given that your two resultset columns are derived from the same column, your question and reasoning are difficult to understand. Why split them into two if you now want to "combine"? – SMor Feb 25 '22 at 12:15

0 Answers0