0

Using a datetime-local form field, and inserting its value into a SQL 2014 smalldatetime field. When querying the database to populate the form field for editing, I'm using 

SELECT FORMAT(myDate, 'yyyy-MM-ddThh:mm:ss') AS myDate

My form field code is:

<input type="datetime-local" name="myDate" 
    id="myDate" required 
    value="<cfoutput>#myDate#</cfoutput>">

When populating the above form field with this database value, it returns the correct date and time BUT it always indicates AM. EXAMPLE: the value in the database table is 2016-11-03 13:09:00 but the value in the form shows as 11/03/2016 01:09 AM

How can I change my SQL format to accurately populate the form field? It should be 11/03/2016 01:09 PM?

Thanks

Leigh
  • 28,765
  • 10
  • 55
  • 103
Brig
  • 13
  • 4

1 Answers1

2

I would leave the formatting in the front end. However, if you change the hours to uppercase it will give you the time in 24 hour format.

SELECT FORMAT(myDate, 'yyyy-MM-ddTHH:mm:ss') AS myDate
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Yes, that did the trick, and now my SQL formatting returns AM or PM correctly into the form field. Silly cases! ;) Thank you, Sean. – Brig Nov 10 '16 at 23:23
  • 1
    *I would leave the formatting in the front end* Agreed. Generally it is best to leave dates - as date *objects*. Converting them to strings, for presentation, is the job of the front end app, ie CF. – Leigh Nov 12 '16 at 20:27