asked on

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.

SQLTriVegg

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

ralmada

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

edrz01

ASKER

I am new to creating functions in SQL, do I put the code into the SQL statement above the Select statement?

SQLTriVegg

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.

ralmada

Yes, run the function code first and then use it in the select as I indicated

ralmada

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

edrz01

ASKER

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.

ralmada

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

ralmada

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

edrz01

ASKER

ramalda - yes I caught that one too. Your formula does yeild the format that is requested. And once again I applaud you.

edrz01

ASKER

I am working on the last piece of the puzzle, adding the current Time Stamp, HHMMSS

ralmada

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

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edrz01

ASKER

The requirement is to have a separate CCYYMMDD column and Time column of the current time.

edrz01

ASKER

the time must be HHMMSS

ralmada

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

edrz01

ASKER

Once again the experts on this team is outstanding. Your solutions have been right on. Thank you once again.!

ralmada

if you want the current time use the getdate() function

select replace(convert(varchar, getdate(), 108), ':', '') from yourtable

Open in new window

lof

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)