Is there any way to format the date of a field in a query?

Question

I am getting a query with a field in an undesired date format (Thu Feb 21 00:00:00 EST 2013) Is there any way to modify this to mm-dd-yyy?

I am using javascript, I found a php way to do it, but sadly it has to be in javascript, so the instruction has to be pretty much the same way it would be in TOAD.

I tried the CONVERT() method and it didn't work. I am not sure I am using it right though

3-digit year? That's weird. Is this a historical database? Anyway, see the Javascript Date methods `getDate`, `getMonth`, and `getYear`. — Barmar, Feb 28 '13 at 21:56
See http://stackoverflow.com/questions/1056728/formatting-a-date-in-javascript — Aaron Kurtzhals, Feb 28 '13 at 21:59

score 1 · Accepted Answer · answered Feb 28 '13 at 21:57

1

The Convert() function will work, but you need to use the correct format code from here: SQL Convert() Function.

SELECT Convert(char(10), @date, 110)

answered Feb 28 '13 at 21:57

Pieter Geerkens

11,775
2
32
52

score 0 · Answer 2 · answered Feb 28 '13 at 21:56

0

Date.js is pretty handy for date formatting.

answered Feb 28 '13 at 21:56

Diodeus - James MacFarlane

112,730
33
157
176

talemyn · Answer 3 · 2013-02-28T22:11:24.643

There are basic Date object functions in JS that you can use.

First, create the date variable:

var date = new Date('your date value');

Then you can access the individual date pieces:

var month = date.getMonth() + 1;  //gets the month . . . it's 0-based, so add 1
var day = date.getDate();         //gets the day of the month
var year = date.getFullYear();    //gets the 4-digit year

Once you have those values, you can concatenate them in any format that you'd like. For a basic mm-dd-yyyy, use:

var formattedDate = month + "-" + day + "-" + year;

There time and timezone values are also available.

score 0 · Answer 4 · answered Feb 28 '13 at 22:09

you probably could try converting to a unix timestamp, then formatting. I havent tested this, and it will probably throw an error, but you get the idea.

var input = your date;
input = input.split(" - ").map(function (date){
return Date.parse(date+"-0500")/1000;
}).join(" - ");

var year = input.getYear();
var month = input.getMonth();
var day = input.getDay();
var hours = input.getHours();
var minutes = input.getMinutes();
var seconds = input.getSeconds();
var formatted = month + " " + day + ", " + year + " at " hours + ':' + minutes + ':' +     seconds;

score 0 · Answer 5 · answered Feb 28 '13 at 22:54

That's a badly mixed up format. There are two basic ways to modify it, one is to just re–order the bits you have, the other is to convert it to a date object and use that to create the new string. Either way, you haven't said what to do with the timezone offset.

Using abbreviations or names for timezones is ambiguous, there is no standard for them and some are duplicted (EST is used for three different timezones). In any case, a simple re–ordering can be:

function formatDate(s) {
  var months = {jan:'01', feb:'02', mar:'03', apr:'04',
                may:'05', jun:'06', jul:'07', aug:'08',
                sep:'09', oct:'10', nov:'11', dec:'12'};
  var s = s.split(' ');
  var d = (s[2] < 10? '0' : '') + s[2]; 

  return months[s[1].toLowerCase()] + '-' + d + '-' + s[5];
}

alert(formatDate('Thu Feb 21 00:00:00 EST 2013')); // 02-21-2013

The output format (mm-dd-yyyy) is ambiguous, consider using something like 21-Feb-2013 or a standard format like ISO8601 (2013-02-21).

If you need to consider the timezone, it will be easier to create a date object, add the offset, then get back the new date. However, you will also need to work out how to convert the string timezone to a number (preferably minutes, but hours is OK) that can be used with the date.

Is there any way to format the date of a field in a query?

5 Answers5