Solved

sql date format

Posted on 2011-09-26
8
214 Views
Last Modified: 2012-05-12
I have a copy of some code i need to adjust. the following code sets the date as 160611
i need to change the date to the following format 20110417.

please ignore the actual dates as they are just examples.
also with my print statement nothing happens.
set dateformat dmy 

--***********************************
--Declare Variables
--***********************************
declare 
	 @Date varchar(10)
	 , @FileDate varchar(8)


SET @FileDate = CONVERT(VARCHAR,
					CASE WHEN DATEPART(DD,@Date) < 10 
						 THEN '0' + CONVERT(VARCHAR,DATEPART(DD,@Date))
						 ELSE CONVERT(VARCHAR,DATEPART(DD,@Date))
					END
				 )
				+ CONVERT(VARCHAR,
					CASE WHEN DATEPART(MM,@Date) < 10 
						 THEN '0' + CONVERT(VARCHAR,DATEPART(MM,@Date))
						 ELSE CONVERT(VARCHAR,DATEPART(MM,@Date))
					END
				 )
				+ CONVERT(VARCHAR,RIGHT(DATEPART(YYYY,@Date),2))


print @FileDate

Open in new window

0
Comment
Question by:aneilg
8 Comments
 
LVL 5

Accepted Solution

by:
DerZauberer earned 250 total points
ID: 36598380
--***********************************
--Declare Variables
--***********************************
declare 
	 @Date varchar(10)
	 , @FileDate varchar(8)


SET @FileDate = CONVERT(VARCHAR,DATEPART(YYYY,@Date))
				+ CONVERT(VARCHAR,
					CASE WHEN DATEPART(MM,@Date) < 10 
						 THEN '0' + CONVERT(VARCHAR,DATEPART(MM,@Date))
						 ELSE CONVERT(VARCHAR,DATEPART(MM,@Date))
					END
				 )
				+ CONVERT(VARCHAR,
					CASE WHEN DATEPART(DD,@Date) < 10 
						 THEN '0' + CONVERT(VARCHAR,DATEPART(DD,@Date))
						 ELSE CONVERT(VARCHAR,DATEPART(DD,@Date))
					END
				 )


print @FileDate

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36598476
If @date was a datetime data type you can just do this:

SET @FileDate = select CONVERT(nvarchar(10), @Date, 112)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36598802
set @filedate=convert(char(8),@date,112)
0
 

Author Comment

by:aneilg
ID: 36598813
thanks guy.
one last question why does my print @FileDate not work.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36598874
It does work. It prints to the messages tab. Use:

Select @FileDate

This returns it as a recordset.
0
 

Author Comment

by:aneilg
ID: 36598882
silly me. thanks.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36599032
btw, here my article to handle date/time correctly:
http://www.experts-exchange.com/A_1499.html
0
 

Author Closing Comment

by:aneilg
ID: 36923375
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

22 Experts available now in Live!

Get 1:1 Help Now