Solved

Date convertion

Posted on 2013-05-15
5
343 Views
Last Modified: 2013-05-22
How can I convert this date format to YYYYMMDD?

2013-04-02 00:00:00.000

to

YYYYMMDD
20130402
0
Comment
Question by:VBdotnet2005
5 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
ID: 39168876
For some reason this works in my SSMS
Declare @dt datetime = '2013-04-02 00:00:00.000'
SELECT REPLACE(CONVERT(date, @dt, 112), '-', '')

Open in new window

and this doesn't
SELECT REPLACE(CONVERT(date, '2013-04-02 00:00:00.000', 112), '-', '')

Open in new window

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 125 total points
ID: 39168915
> For some reason this works in my SSMS
You are converting it to 'Date', thats why it doesn't strip of the '-'s,

SELECT CONVERT(varchar, cast('2013-04-02 00:00:00.000' as datetime), 112)
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39170449
In case you expect your output to be string, I'd suggest this:

Declare @dt datetime = '2013-04-02 00:00:00.000'
SELECT CONVERT(char(8), @dt, 112)

Or perhaps you're expecting a number? Then:

Declare @dt datetime = '2013-04-02 00:00:00.000'
SELECT CONVERT(int, CONVERT(char(8), @dt, 112))
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 125 total points
ID: 39171244
if SQL server 2012 then


Select Format(GETDATE(),'yyyyMMdd')
0
 
LVL 12

Assisted Solution

by:sachitjain
sachitjain earned 125 total points
ID: 39186434
SQL Server any version, this would surely work

declare @dt datetime
set @dt = getdate()
select convert(varchar(4), year(@dt)) + right('0' + convert(varchar(2), month(@dt)), 2) + right('0' + convert(varchar(2), day(@dt)), 2)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

948 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

20 Experts available now in Live!

Get 1:1 Help Now