Link to home
Create AccountLog in
Avatar of ExpertKapur
ExpertKapur

asked on

Date Time and VARCHAR

Hi,
I have a variable like @OrderTime varchar(8) now this variable I am using to add a timestamp to a file. Like SET @FileName = 'xyz' + @OrderTime.+ '.xml'
My file should look like this xyz20101229130446470.xml . How Can I do that? I want everything except the millisecond part so 470 will be removed and it should be xyz20101229130446.xml
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Try this and see if it helps:

I@FileName = 'xyz' + SUBSTRING(CONVERT(varchar, @OrderTime, 100), 13, 5)+ '.xml'

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Som Tripathi
Som Tripathi
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
try

select @OrderTime  = replace(replace(replace(convert(varchar(19),getdate(),21),'-',''),':',''),' ','')
Actually this is better:

select FileName = 'xyz' + CONVERT(CHAR(10),@OrderTime, 23) + SUBSTRING(CONVERT(varchar, @OrderTime, 100), 13, 5)+ '.xml' from yourtable

Open in new window

Guys, I said "better" compared to the original code I posted; not to you guys code.

Just wanted to clarify to avoid confusion.
Avatar of ExpertKapur
ExpertKapur

ASKER

Thanks!