Solved

How to convert a date to a certain string

Posted on 2013-11-25
3
400 Views
Last Modified: 2013-11-26
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
0
Comment
Question by:damixa
3 Comments
 

Author Comment

by:damixa
ID: 39675940
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39675979
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39676433
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now