date formatting - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

date formatting

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2009-05-08 : 09:21:45

hello,
we are trying to generate an invoice ID, based on date and transctionID

here is my code to get the date

SELECT convert(datetime, GETDATE(),101)

But it returns the normal MS SQL date, how can i get the date back as

YYYYMMDDMMSS

Thanks
MM

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-08 : 09:32:55

This should be done in the front end since you are converting the date to a string

declare @date datetime
set @date = getdate()

select replace(replace(replace(convert(varchar(19),@date,121),'-',''),':',''),' ','')

Jim

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 09:45:25

And without milisec:


select convert(varchar(4),YEAR(getdate())) + 
       right('00'+convert(varchar(2),MONTH(getdate())),2) +
       RIGHT('00'+convert(varchar(2),day(getdate())),2) +
       RIGHT('00'+convert(varchar(2),datepart(MINUTE,(getdate()))),2) +
       RIGHT('00'+convert(varchar(2),datepart(SECOND,(getdate()))),2)

No, you're never too old to Yak'n'Roll if you're too young to die.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2009-05-08 : 23:19:00

select convert(varchar(19),getdate(),112)+ replace(convert(varchar(19),getdate(),108),':','')

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-05-09 : 02:46:10

Hi see this link, i will be more helpful to u

http://www.sql-server-helper.com/tips/date-formats.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 04:37:57