The code works ... but is it right? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

The code works ... but is it right?

Author

Topic

LaurieCox

158 Posts

Posted - 2006-12-11 : 15:22:10

Hi,

Is there a better (more elegant) way to build the file name than the following code:


/* File name has format mmddhhmmDX.748 with all time/date values zero padded */

DECLARE @FileName char(14)
DECLARE @out_dte datetime
DECLARE @CharDate char(8)
DECLARE @CharTime char(8)

set @out_dte = '2006-08-03 04:05:12.670'
set @CharDate = convert(char(8),@out_dte,1)
set @CharTime = convert(char(8),@out_dte,8)


set @FileName = substring(@CharDAte,1,2) + 
		   substring(@CharDate,4,2) + 
		   substring(@CharTime,1,2) + 
		   substring(@CharTime,4,2) + 
		   'DX.748' 
select @FileName

/* Expected Result: 08030405DX.748 */

My first attempt was this:


DECLARE @FileName char(14)
DECLARE @out_dte datetime

set @out_dte = '2006-08-03 04:05:12.670'
set @FileName = cast(DATEPART(mm,@out_dte) as char(2)) +
		   cast(DATEPART(dd,@out_dte) as char(2)) + 
		   cast(DATEPART(HH,@out_dte) as char(2)) + 
		   cast(DATEPART(mm,@out_dte) as char(2)) +
		   'DX.748'

	
 select @FileName

… but then date and time values were left justified spaced filled.
Example output: 8 3 4 8 DX.748

I don't just want to get code that works. I want to learn how to write the best possible code.

Thanks,

Laurie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 15:31:24

Something like this, maybe?

DECLARE @out_dte datetime

set @out_dte = '2006-08-03 04:05:12.670'

select	@out_dte,
	replace(replace(replace(substring(convert(varchar, @out_dte, 120), 6, 11), ' ', ''), '-', ''), ':', '') + 'DX.748'

Peter Larsson
Helsingborg, Sweden

LaurieCox

158 Posts

Posted - 2006-12-11 : 15:34:12

cool! much more elegant ... thank you

Laurie

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-12-15 : 10:10:10

Hi all,

Just for fun I thought I'd try this a different way and compare speeds.

Here's my test code...

--test code (un/comment alternatives to test the 2 methods)
declare @out_dte datetime
set @out_dte = '2006-08-03 04:05:12.670'

declare @i int
declare @v varchar(20)
set @i = 0
while @i < 1000000
begin
    set @i = @i + 1
    set @v = right(cast(100000000 + 1000000 * datepart(m, @out_dte) + 10000 * datepart(d, @out_dte) + 100 * datepart(hh, @out_dte) + datepart(mi, @out_dte) as char(9)), 8) + 'DX.748' --numerical way
    --set @v = replace(replace(replace(substring(convert(varchar, @out_dte, 120), 6, 11), ' ', ''), '-', ''), ':', '') + 'DX.748' --string way
end

The upshot (I believe) is that the 'numerical' way is about 4 times faster, but about 50% longer code.

I'm not sure which is more elegant...

Ryan

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources