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

convert Date() to CCYYMMDD format in SQL

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
edrz01
Asked:
edrz01
  • 9
  • 7
  • 2
  • +1
1 Solution
 
SQLTriVeggCommented:
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
 
ralmadaCommented:
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
 
edrz01Author Commented:
I am new to creating functions in SQL, do I put the code into the SQL statement above the Select statement?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
SQLTriVeggCommented:
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
 
ralmadaCommented:
Yes, run the function code first and then use it in the select as I indicated
0
 
ralmadaCommented:
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
 
edrz01Author Commented:
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
 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
edrz01Author Commented:
ramalda - yes I caught that one too. Your formula does yeild the format that is requested. And once again I applaud you.
0
 
edrz01Author Commented:
I am working on the last piece of the puzzle, adding the current Time Stamp, HHMMSS
0
 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
edrz01Author Commented:
The requirement is to have a separate CCYYMMDD column and Time column of the current time.
0
 
edrz01Author Commented:
the time must be HHMMSS
0
 
ralmadaCommented:
oh so you just do like this, no need to create any function
select replace(convert(varchar, yourdatetimecolumn, 108), ':', '') from yourtable
 
0
 
edrz01Author Commented:
Once again the experts on this team is outstanding. Your solutions have been right on. Thank you once again.!
0
 
ralmadaCommented:
if you want the current time use the getdate() function
select replace(convert(varchar, getdate(), 108), ':', '') from yourtable

Open in new window

0
 
lofCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 9
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now