Two-digit date format in SQL
I have a table field AccID
where I need to concatenate Name
with Date
like "MyName-010415" in a SQL query.
Date format 01-04-2015
or 01/04/2015
. But I want to show him how 010415
.
For the date part to get the format you want, try this:
SELECT
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DD, accid) AS VARCHAR(2)), 2) +
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(MM, accid) AS VARCHAR(2)), 2) +
RIGHT(DATEPART(YY, accid), 2) AS CustomFormat
FROM yourtablename
...
DATEPART(DD, accid)
will give you a part of the day and the same for mm
and yy
will give you month and year. Then I added functions RIGHT(REPLICATE('0', 2) + CAST(... AS VARCHAR(2)), 2)
to add a leading zero instead of 1
it will be 01
.
- SQL Fiddle Demo
As @ bernd-linde suggested , you can use this function to combine it with a part of the name, for example:
concat(Name, ....) AS ...
Also you can simply SELECT
or UPDATE
whichever you are looking for.
As in the @ bernd-linde fiddle .
I'm not sure which language you are using. Let's take php as an example.
$AccID = $name.'-'.date('dmy');
OR before you save this data format, before you insert data into the database .. or you can write a trigger to insert.
You need to use DATE_FORMAT to change the format of your date and CONCAT to marge name with date.
Example:
SELECT CONCAT(name, '-', DATE_FORMAT(field,'%m%d%y'))
FROM tbl