?
Solved

convert Date() to CCYYMMDD format in SQL

Posted on 2010-01-12
19
Medium Priority
?
4,556 Views
Last Modified: 2012-08-14
I have to convert Date() to CCYYMMDD format using MSSQL. i.e. if today's date yields 01/12/2001 CC date should converted respectively. I have a formula that works in MS Access in a module:
Public Function FormatCCDate(inputdate As Date)
'reformats a date in mm/dd/yyyy format to cyymmdd
    Dim lngyear As Long
    Dim lngday As Long
    Dim lngmonth As Long
    lngyear = DatePart("yyyy", inputdate)
    lngyear = lngyear - 1900
    lngday = DatePart("d", inputdate)
    lngmonth = DatePart("m", inputdate)
    FormatCCDate = (lngyear * 10000) + (lngmonth * 100) + (lngday)
End Function
This works fine in Access within a query, but I would like to do this in MS SQL 2000.
0
Comment
Question by:edrz01
[X]
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
  • 9
  • 7
  • 2
  • +1
19 Comments
 
LVL 3

Expert Comment

by:SQLTriVegg
ID: 26296082
Try this, susbstituting your date for each occurrence of GetDate()
SELECT       LEFT(CONVERT(varchar, GetDate(), 120), 4)
                  + Substring(CONVERT(varchar, GetDate(), 120), 6, 2)
                  + Substring(CONVERT(varchar, GetDate(), 120), 9, 2)

Or encapsulate this login into a function.  See attached script.            
fnConvertToCCYYMMDD.txt
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26296085
Use the function like this
select dbo.FormatCCDate(yourcolumn) from yourtable

create Function dbo.FormatCCDate(@inputdate Datetime)
returns bigint
as
begin
	'reformats a date in mm/dd/yyyy format to cyymmdd

	declare @lngyear bigint
    	declare @lngday bigint
	declare @lngmonth bigint

	set @lngyear = year(inputdate)
	set @lngyear = lngyear - 1900
	set @lngday = day(inputdate)
    	set @lngmonth = month(inputdate)
    	return( (@lngyear * 10000) + (@lngmonth * 100) + (@lngday))
end;

Open in new window

0
 

Author Comment

by:edrz01
ID: 26296257
I am new to creating functions in SQL, do I put the code into the SQL statement above the Select statement?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 3

Expert Comment

by:SQLTriVegg
ID: 26296303
To create the function, just run the code in a query window.  Be sure to set the database context first (the dropdown at the top for which database you are in).

In the attached file containing the function, there is a section called "Usage" which gives 3 examples on how to use the function.
Yes, you will use the function in a SELECT statement.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26296390
Yes, run the function code first and then use it in the select as I indicated
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26296444
Now if you just want the date for instance 01/12/2010 to look like this: 20100112
just do a select like this:
select convert(varchar, yourdatecolumn, 112) from yourtable
No need to create any function
0
 

Author Comment

by:edrz01
ID: 26296476
The results I received is: 20100112

However, if I'm not understanding the users request, i.e. CCYYMMDD is Century, Year, Month, Day. When I use the MS Access code, which is very similar to 'ralmada' the results yield: 1100112

And ralmada, I attempted to create your function...but it errors on 'year', 'month', 'day'
however, I did REM the noteline from ' to /* */ and removed the ; at the end.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26296576
Sorry missed '@' there
create Function dbo.FormatCCDate(@inputdate Datetime) 
returns bigint 
as 
begin 
        --reformats a date in mm/dd/yyyy format to cyymmdd 
 
        declare @lngyear bigint 
        declare @lngday bigint 
        declare @lngmonth bigint 
 
        set @lngyear = year(@inputdate) 
        set @lngyear = lngyear - 1900 
        set @lngday = day(@inputdate) 
        set @lngmonth = month(@inputdate) 
        return( (@lngyear * 10000) + (@lngmonth * 100) + (@lngday)) 
end;

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 26296671
Still missed one @! In SQL Variables starts with '@'
create Function dbo.FormatCCDate(@inputdate Datetime)  
returns bigint  
as  
begin  
        --reformats a date in mm/dd/yyyy format to cyymmdd  
  
        declare @lngyear bigint  
        declare @lngday bigint  
        declare @lngmonth bigint  
  
        set @lngyear = year(@inputdate)  
        set @lngyear = @lngyear - 1900  
        set @lngday = day(@inputdate)  
        set @lngmonth = month(@inputdate)  
        return( (@lngyear * 10000) + (@lngmonth * 100) + (@lngday))  
end;

Open in new window

0
 

Author Comment

by:edrz01
ID: 26296780
ramalda - yes I caught that one too. Your formula does yeild the format that is requested. And once again I applaud you.
0
 

Author Comment

by:edrz01
ID: 26296796
I am working on the last piece of the puzzle, adding the current Time Stamp, HHMMSS
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26296993
Current time? or the inputvalue one?

create Function dbo.FormatCCDate(@inputdate Datetime)   
returns varchar
as   
begin   
        --reformats a date in mm/dd/yyyy format to cyymmdd   
   
        declare @lngyear bigint   
        declare @lngday bigint   
        declare @lngmonth bigint   
   
        set @lngyear = year(@inputdate)   
        set @lngyear = @lngyear - 1900   
        set @lngday = day(@inputdate)   
        set @lngmonth = month(@inputdate)   
        return( cast(((@lngyear * 10000) + (@lngmonth * 100) + (@lngday)) as varchar) + convert(varchar, @inputdate, 108) )   
end;

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 26297007
sorry
create Function dbo.FormatCCDate(@inputdate Datetime)    
returns varchar(15)
as    
begin    
        --reformats a date in mm/dd/yyyy format to cyymmdd    
    
        declare @lngyear bigint    
        declare @lngday bigint    
        declare @lngmonth bigint    
    
        set @lngyear = year(@inputdate)    
        set @lngyear = @lngyear - 1900    
        set @lngday = day(@inputdate)    
        set @lngmonth = month(@inputdate)    
        return( cast(((@lngyear * 10000) + (@lngmonth * 100) + (@lngday)) as varchar) + convert(varchar, @inputdate, 108) )    
end;

Open in new window

0
 

Author Comment

by:edrz01
ID: 26297019
The requirement is to have a separate CCYYMMDD column and Time column of the current time.
0
 

Author Comment

by:edrz01
ID: 26297035
the time must be HHMMSS
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26297143
oh so you just do like this, no need to create any function
select replace(convert(varchar, yourdatetimecolumn, 108), ':', '') from yourtable
 
0
 

Author Closing Comment

by:edrz01
ID: 31676253
Once again the experts on this team is outstanding. Your solutions have been right on. Thank you once again.!
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26297179
if you want the current time use the getdate() function
select replace(convert(varchar, getdate(), 108), ':', '') from yourtable

Open in new window

0
 
LVL 10

Expert Comment

by:lof
ID: 26297210
it's accepted already but have a look at this one, no function way to get CCYYMMDD

select convert(varchar(10),convert(int,year(getdate())/100 )+1) + right(convert(varchar(10), getdate(), 112),6)

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month10 days, 11 hours left to enroll

765 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