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

sql date format

please note the dates get added everyday and are not static.
my source table has the following date. 2011-10-04 00:00:00.000

the following code below produces the dta as follows. 041011.xls
i need to change it from ddmmyy to yyyymmdd.
+ CASE WHEN LEN(CONVERT(VARCHAR,DATEPART(dd,@date))) = 1 
		  THEN '0' + CONVERT(VARCHAR,DATEPART(dd,@date)) 
		  ELSE CONVERT(VARCHAR,DATEPART(dd,@date)) END
	+ CASE WHEN LEN(CONVERT(VARCHAR,DATEPART(mm,@date))) = 1 
		  THEN '0' + CONVERT(VARCHAR,DATEPART(mm,@date)) 
		  ELSE CONVERT(VARCHAR,DATEPART(mm,@date)) END
	+ CONVERT(VARCHAR,RIGHT(DATEPART(yyyy,@date),2))
	+ '.xls" "exit"'

Open in new window

0
aneilg
Asked:
aneilg
1 Solution
 
EyalCommented:

	+ CONVERT(VARCHAR,RIGHT(DATEPART(yyyy,@date),4))
	+ CASE WHEN LEN(CONVERT(VARCHAR,DATEPART(mm,@date))) = 1 
		  THEN '0' + CONVERT(VARCHAR,DATEPART(mm,@date)) 
		  ELSE CONVERT(VARCHAR,DATEPART(mm,@date)) END
+ CASE WHEN LEN(CONVERT(VARCHAR,DATEPART(dd,@date))) = 1 
		  THEN '0' + CONVERT(VARCHAR,DATEPART(dd,@date)) 
		  ELSE CONVERT(VARCHAR,DATEPART(dd,@date)) END
	+ '.xls" "exit"'

Open in new window

0
 
aneilgAuthor Commented:
dude your a Ledged. i hate dates. saved me so much messing round. got deadlines to meet.

thanks big time.
0
 
pritamduttCommented:
This will work for you
Select Convert(varchar,DATEPART(yyyy,'2011-01-04 00:00:00.000'))+RIGHT('00' + Convert(varchar,DATEPART(mm,' 2011-01-04 00:00:00.000')),2) + RIGHT('00' + Convert(varchar,DATEPART(dd,' 2011-01-04 00:00:00.000')),2) 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
date and time: don't be scared :)
http://www.experts-exchange.com/A_1499.html
0
 
aneilgAuthor Commented:
perfect
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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