CODEWITHSUNDEEP
sqlt-sql
Abdul
Abdul

Reputation: 2120

Two Digit date format in SQL

I have a table field AccID where I have to concatenate Name with Date like 'MyName-010415' in SQL query.

Date format is 01-04-2015 or 01/04/2015. But I want to display it like 010415.

Upvotes: 3

Views: 1815

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

For the date part, to get the format you want you, 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
...

The DATEPART(DD, accid) will give you the day part and the same for mm and yy will give you the month and the year parts. Then I added the functions RIGHT(REPLICATE('0', 2) + CAST(... AS VARCHAR(2)), 2) to add the leading zero, instead of 1 it will be 01.

As @bernd-linde suggested, you can use this function to concatenate it with the name part like:

concat(Name, ....) AS ...

Also you can just SELECT or UPDATE depending on what you are looking for.

As in @bernd-linde's fiddle.

Upvotes: 2

user1231342435346354
user1231342435346354

Reputation: 47

You need to use DATE_FORMAT to change format of your date and CONCAT to marge name with date.

Example:

SELECT CONCAT(name, '-', DATE_FORMAT(field,'%m%d%y')) 
FROM tbl

Upvotes: 0

m82amjad
m82amjad

Reputation: 248

I am not sure which language you are using. Let take php as an example. 

           $AccID  = $name.'-'.date('dmy');

OR before you save this data format the date before you insert the data in database.. or you can write a trigger on insert.

Upvotes: 0

Related Questions