Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql date format

Posted on 2011-09-26
8
216 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:aneilg
ID: 36598813
thanks guy.
one last question why does my print @FileDate not work.
0
 
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 143

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

790 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