get date month year in Sq from string

How can I get varchar ('3/1/2015' or '3/10/2015') in this format in sql?
Date = 01
Month = 03
Year = 2015
VBdotnet2005Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naitik GamitSoftware DeveloperCommented:
TRY AS:

select
datepart(month,getdate())
datepart(year,getdate())
,datename(month,getdate())
VBdotnet2005Author Commented:
Hi, the parameter is varchar, not date.
PortletPaulEE Topic AdvisorCommented:
What are you trying to achieve with the concatenation?
and why are you trying to suppress leading zeros?

the SAFEST literal format for dates in SQL Serevr is YYYYMMDD e.g.

select * from Table1 where TransDate > '20150301'

(& If you are inserting into a table you also would not use '3/1/2015' ) e.g.

CREATE TABLE Table1
	([TransDate] datetime)
;
	
INSERT INTO Table1
	([TransDate])
VALUES
	('20150301')
;

Open in new window


so if:

pDate = '01'
pMonth = '03'
pYear = '2015'

concatDate = pYear + pMonth + pDate = '20150301'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Vikas GargAssociate Principal EngineerCommented:
Hi,

Hope this would help

DECLARE @ST VARCHAR(50) = '3/1/2015'

SELECT CONVERT(DATE,@ST) , datepart(month,CONVERT(DATE,@ST)) Mon,
datepart(year,CONVERT(DATE,@ST)) Yr,
datename(DAY,CONVERT(DATE,@ST)) Dy

Open in new window

Goodangel MatopeSoftware ArchitectCommented:
You create a function which creates a string version of the date like so:

CREATE FUNCTION dbo.fnVarcharDate(@lvDay INT, @lvMonth INT, @lvYear INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @WorkingDate VARCHAR(20)
DECLARE @InterimDate DATETIME;
DECLARE @VarcharDate VARCHAR(20)

SET @WorkingDate = CAST(@lvMonth AS VARCHAR) + '/' + CAST(@lvDay AS VARCHAR) + '/' + CAST(@lvYear AS VARCHAR);

RETURN @WOrkingDate;

END

GO

Open in new window


Then you can call the function by passing day, month and year values, and it will produce the required date, like so (I have used 1 april 2015 as an example.

SELECT dbo.fnVarCharDate(1,4,2015)

Open in new window

Monika BhartiSr. AnalyticsCommented:
Hi,

There are many date formats, as you want to set the date in this format (dd/mm/yyyy) which uses the style value “103”. And gives you the Output in this format 03/01/2015

To use this date format you can put the value in the following syntax
CONVERT(data_type(length),expression,style)

for example

SELECT CONVERT(VARCHAR(10), GETDATE(), 103)

Open in new window


Note : 103 define the format dd/mm/yyyy which you want. If you want to see the more date formats visit this link
Scott PletcherSenior DBACommented:
Is it 1 parameter or 3?

If it's three, such as:
@Date = 01
@Month = 03
@Year = 2015

Then you can do this:
SUBSTRING(@Month, CHARINDEX('%[^0]%', @Month), 2) + '/' +
SUBSTRING(@Date, CHARINDEX('%[^0]%', @Date), 2) + '/' +
@Year

If it's one parameter, what is the exact format?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.