0

I have a date in my table that is in there as 2016-12-05 etc

When I use a select statement to get that value back however it comes back in a really odd way: Mon Dec 5 00:00:00 UTC 2016

How do I get this to come back in YYYY-MM-DD?

Edit:

Using JSCRIPT. Select command is 

SELECT AccessDate from screens.signoff WHERE BadgeNo = '" + BadgeNo + "'"

BadgeNo being a variable defined above somewhere

Jack Gillard
  • 13
  • 2
  • 2
    maybe if you share some code... ;) – Lelio Faieta Dec 05 '16 at 10:02
  • 1
    Show us how you query the table for this date column – RiggsFolly Dec 05 '16 at 10:02
  • 1
    What datatype is this column in your schema – RiggsFolly Dec 05 '16 at 10:03
  • 1
    This seems to be a formatting issue only, without any hint as to how you retrieve the data, it is impossible for us to tell what may have gone wrong. – Shadow Dec 05 '16 at 10:10
  • SQL stores date information in the database usually as an offset from a particular point in time. Selecting the date out converts it to a string format for display - it depends where you are displaying this date but the general rule is, convert to a string at the last moment possible. We can't tell what your "last moment" is because the question doesn't include where or how you want to display the value... – Charleh Dec 05 '16 at 10:18
  • In this case you probably want to look at: http://stackoverflow.com/questions/3552461/how-to-format-a-javascript-date, mostly the second answer - using `moment.js` if possible will save you a load of headache. The date/time implementation in JavaScript and variations is generally poor. – Charleh Dec 05 '16 at 10:27
  • which data base client you use. there should be option to change date time format – Chamly Idunil Dec 05 '16 at 10:33

3 Answers3

0

I think this can help you.

for MySQL

DATE_FORMAT(STR_TO_DATE(myValue, '%d.%m.%y'), '%Y-%m-%d')

look for str_to_date : http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date

edit: for MSSQL 

SELECT CONVERT(char(10), GetDate(),126)

why 126? look there: https://msdn.microsoft.com/en-us/library/ms187928.aspx

fable
  • 162
  • 12
  • 1
    This is MySql as specified by the tags... pretty sure MS didn't work on this one :) – Charleh Dec 05 '16 at 10:14
  • 1
    @Maharshi I disagree: `"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GetDate(),126)' at line 1"`. This is MySql not MSSQL. – Charleh Dec 05 '16 at 10:19
  • @Charleh I am talking about MSSQL, I agree with you about MySQL. Pardon me, I missed to state it in previous comment. – Maharshi Dec 05 '16 at 10:32
  • @Charleh I edited for MySQL :) Maybe this can help? – fable Dec 05 '16 at 10:36
0

Use DATE_FORMAT() function for this

In your case, it will be :

SELECT DATE_FORMAT(AccessDate, '%Y-%m-%d') as AccessDate from screens.signoff WHERE BadgeNo = '" + BadgeNo + "'"

refer this documentation MySQL DATE_FOMAT() reference

Hope it helps.

Nitin
  • 290
  • 2
  • 12
Anish Chikodi
  • 96
  • 6
-1

Use this:

$Date_database= $row['date']; // your database code
$Date= date('Y-m-d', strtotime($Date_database)); // change to right date format

Date Contains 2016-12-05 now

Jbadminton
  • 1
  • 3
  • 19
  • 42