• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

convert date from 04/20/2014 to text or int 140420

I'm going a bit blank on trying to make the conversion. The other thing is that the output needs to be a string or int, not date format

thanks in advance.
0
damixa
Asked:
damixa
1 Solution
 
lcohanDatabase AnalystCommented:
SELECT cast(datepart(year, '04/20/2014') as sysname) + cast(datepart(month, '04/20/2014') as sysname) + cast(datepart(day, '04/20/2014') as sysname);
0
 
damixaAuthor Commented:
thanks for the answer. Is there a way to incorporate the leading zero as well?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Copy-paste the below code into your SSMS, execute it to verify it meets your requirement, then motify
Declare @dt date = '2014-04-20'

SELECT RIGHT(CAST(YEAR(@dt) as varchar(4)),2)
   + RIGHT('0' + CAST(MONTH(@dt) as varchar(2)),2)
   + RIGHT('0' + CAST(DAY(@dt) as varchar(2)),2)

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw I executed the code in the first comment in this question in SQL 2012 SSMS and it returned 2014420, which is not correct.
0
 
Scott PletcherSenior DBACommented:
For char/varchar:
SELECT CONVERT(varchar(8), date_column, 112)

For int:
SELECT CAST(CONVERT(varchar(8), date_column, 112) AS int)
0
 
PortletPaulCommented:
YYMMDD is style 12

convert(varchar, your_data_here ,12) -- varchar

convert(int,convert(varchar, your_data_here ,12)) -- now int

see:
http://www.experts-exchange.com/Database/MS-SQL-Server/A_12315-SQL-Server-Date-Styles-formats-using-CONVERT.html
0
 
damixaAuthor Commented:
Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now