Clever Geek Handbook

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

.

+3


source to share


3 answers


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

.



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 .

+2


source


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.

0


source


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
0


source






More articles:


All Articles