How to convert a date to a certain string

I have a date in the format 11/14/13 22:28

I need to convert it into 7884111413 in Sql Server query.

where 7884 is a number that I need to put in front of the date and 111413 is the date

thanks in advance
damixaAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor Commented:
11/14/13 22:28
as there cannot be a 14th Month this appears to be MM/DD/YY

but you then ask:
Anybody knows how to get the year at the end (so ddmmyy instead of yymmdd)

choose the one you want from these:
Declare @dt datetime = '20131114 22:28'

SELECT
  '7884' + replace(convert(varchar, @dt ,3),'/','') as "7884 ddmmyy"
, '7884' + replace(convert(varchar, @dt ,1),'/','') as "7884 mmddyy"

Open in new window

0
 
damixaAuthor Commented:
I was able to do this

(CAST ('7884' as varchar) + cast(convert(varchar, trans_date,12)as varchar))

Open in new window


my only problem is that type 12 gives me the year at the begining and not at the end.

Anybody knows how to get the year at the end (so ddmmyy instead of yymmdd)
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Looking through PortletPaul's article on SQL Date Types I didn't see ddmmyy with no delimeters anywhere, so let's roll our own...
Declare @dt datetime = '11/14/13 22:28'

SELECT '7884' +
	RIGHT('0' + CAST(DATEPART(d, @dt) as varchar(2)),2) + 
	RIGHT('0' + CAST(DATEPART(m, @dt) as varchar(2)),2) + 
	RIGHT(CAST(YEAR(@dt) as char(4)),2) 

Open in new window

0
All Courses

From novice to tech pro — start learning today.