How to Get the Month and Day With Leading 0'S in SQL (E.G. 9 => 09)

How do I get the month and day with leading 0's in SQL? (e.g. 9 => 09)

For SQL Server 2012 and up , with leading zeroes:

 SELECT FORMAT(GETDATE(),'MM')

without:

SELECT    MONTH(GETDATE())

Get mySQL MONTH() to use leading zeros?

Use the following instead:

DATE_FORMAT(`datetime_added`,'%Y-%m')

Explanation:

DATE_FORMAT() function lets you format the date anyway you like using specifiers described in the table below (taken verbatim from documentation). So a format string '%Y-%m' means: "A full year (4 digits), followed by a dash (-), followed by a two-digit month number".

Note that you can specify the language used for day/month names by setting lc_time_names system variable. Extremely useful. Refer to documentation for more details.

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character
%x	x, for any “x” not listed above

Get 2 Digit Number For The Month

there are different ways of doing it

Using RTRIM and specifing the range:

SELECT RIGHT('0' + RTRIM(MONTH('12-31-2012')), 2);

Using Substring to just extract the month part after converting the date into text

SELECT SUBSTRING(CONVERT(nvarchar(6),getdate(), 112),5,2)

see Fiddle

There may be other ways to get this.

Getting Month and Day from a date

SELECT CONVERT(CHAR(5), GETDATE(), 10)

Result:

05-23

Leading Zero when extract Day and Month

One option uses LPAD:

execute format ($f$COPY tmp_x FROM 'D:\Programs\PS\download_files_from_ftp_avtomat\files\LOG_%s_%s_%s.csv'
(header, FORMAT CSV, DELIMITER ',', NULL ' ', ENCODING 'WIN1251');
$f$,
    lpad(extract(day from now())::text, 2, '0'),
    lpad(extract(month from now())::text, 2, '0'),
    extract(year from now()));

The year would always be a fixed width four digit number, unless you plan to work with data which existed before computers were around.

How to get year/month/day from a date object?

var dateObj = new Date();
var month = dateObj.getUTCMonth() + 1; //months from 1-12
var day = dateObj.getUTCDate();
var year = dateObj.getUTCFullYear();

newdate = year + "/" + month + "/" + day;

or you can set new date and give the above values

PHP Regex to check date is in YYYY-MM-DD format

Try this.

$date="2012-09-12";

if (preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$date)) {
    return true;
} else {
    return false;
}