Correct format for 2000/01/01 13:12:45

[timscronin]

Am trying to come up with correct format for date to show
yyyymmddhhmmss. Tried to convert to char with 112 and 120. Any help appreciated

 

[JamesLean]

SELECT CONVERT(varchar(20), getdate(), 120)

You can use the Replace function to get rid of spaces, - and : if you need to. --James

 

[grahams]

select CONVERT(varchar, myDate, 120) as TheDate
etc

Is this what you meant?
HTH,
Graham

 

[timscronin]

Close, however I just need the numbers
no dashes or spaces.
I tried
declare @date datetime
declare @vdate char(10)
set @date = getdate()
set @vdate = convert(char(18), @date, 112)
select @vdate
and you get 20030217, no hhmmss, that's the part i am looking for

 

[JamesLean]

This will work:

SELECT CONVERT(varchar(20), getdate(), 112) + Replace(CONVERT(varchar(20), getdate(), 108), ':', '')

--James

 

[grahams]

That's because your CONVERT is using 112, which is yyyymmdd - the time was never there. Try using 120, and see BOL CONVERT for the various formats.

 

[timscronin]

Thanks, you qualify as a genius

 

[tlbroadbent]

timscronin,

When you give someone a virtual "pat on the back," make sure you let them know who is the recipient. From your comment, I can't determine who gave you the answer you used. If you haven't read the FAQs listed below, I recommend that you do. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[LonnieJohnson]

HOW DO YOU CONVERT, FORMAT OR GET IT TO DISPLAY JUST 01/01/03 INSTEAD OF THAT LONG DATE AND TIME. lonniejohnson@prodev.com
ProDev, MS Access Applications B-) ,

http://wwww.prodev.us

May God blow your mind with His Glory in 2003.

 

[sunila7]

Hi,

u can use this stmt to do that....

for mm/dd/yy
SELECT CONVERT(varchar(8), getdate(), 1)

for dd/yy/yy
SELECT CONVERT(varchar(8), getdate(), 3)

Look at this link on Terry's site for more info

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm

Sunil

 

[LonnieJohnson]

Thanks!!! lonniejohnson@prodev.com
ProDev, MS Access Applications B-) ,

http://wwww.prodev.us

May God blow your mind with His Glory in 2003.