July 21, 2008 at 8:00 am
Im trying to convert the getdate() from datetime into a char string so I can use it to dynamically name a backup file along the lines of simon_20080714_14-59-23.bak. Come up with working on the code Ive shown below but it seems very inelegant and clunky looking .. any improvements happily received ! si
declare
@startString VARCHAR (2),
@length SMALLINT,
@startStringLength SMALLINT,
@month char(2),
@day char(2),
@secs char(2),
@mydate varchar(20)
set @startString = ltrim(rtrim((cast(datepart(mm,getdate())as char(2)))))
set @length = 2
SET @startStringLength = LEN (@startString)
SELECT @startString = (REPLICATE (0, 2 - @startStringLength) + (@startString))
set @month = @startString
set @startString = ltrim(rtrim((cast(datepart(dd,getdate())as char(2)))))
set @length = 2
SET @startStringLength = LEN (@startString)
SELECT @startString = (REPLICATE (0, 2 - @startStringLength) + (@startString))
set @day = @startString
set @startString = ltrim(rtrim((cast(datepart(ss,getdate())as char(2)))))
set @length = 2
SET @startStringLength = LEN (@startString)
SELECT @startString = (REPLICATE (0, 2 - @startStringLength) + (@startString))
set @secs = @startString
set @mydate = cast(datepart(yy,getdate())as char(4))+@month+@day+'_'+ltrim(rtrim(cast(datepart(hh,getdate())as char(2))))+'-'+ltrim(rtrim(cast(datepart(mi,getdate())as char(2))))+'-'+@secs
print (@mydate) -- just to show it works !
July 21, 2008 at 8:10 am
Something along these lines...
select convert(varchar(8), getdate(), 112) + '_' +
replace(substring(convert(varchar(20), getdate(), 113), 13, 8), ':', '-')
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 21, 2008 at 8:15 am
a. thank you very much !
b. feel slightly sheepish at having over engineered it beyond belief !
~si
July 22, 2008 at 2:48 am
You can make it somewhat shorter by using style 108 instead of 113.
select convert(char(8), getdate(), 112) + '_' +
replace(convert(char(8), getdate(), 108), ':', '-')
or
select replace(replace(replace(convert(char(19), getdate(), 120), '-', ''), ' ', '_'), ':', '-')
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply