Date Problem

This cannot be done in one convert, but it is possible
in a few steps:

select convert(char(12),(convert(char(8),colname,112) + convert(char(2),datepart(hh,colname)) + convert(char(2),datepart(mi,colname'))))

There only remains a problem with zeros, if your time is
e.g. 09:04 AM and your output would look like
'200001119 4 '

If you do not have to do it in a single query it's best to put each step in a variable and check for the value to be > 9

like select @var = datepart(hh,colname)

if @var < 10
   select @char_hour = '0' + convert(char(1), @var)

etc.

Hope this helps...

Arjan

declare @mydate datetime
select  @mydate = getdate()

select datename(yy,mydate@)+rtrim(convert(char,datepart(mm,@mydate))+datename(dd,@mydate)+datename(hh,@mydate)+datename(mi,@mydate)

this works perfectly fine

Thanks
Amit Agarwal

Sorry Amit but my output looks like
-                                                                                                                                                      
20001                             121453                                                                                                              
for your query...

If I use a time like 9:04 AM it gets even worse:
-                                                                                                                                                      
20001                             1294                                                                                                                
I don't think this will be it.

What amitpagarwal given is far from the required answer?
there is a lot of space in the month field.
rtrim is not serving the required purpose.
required format is "yyyymmddhhmm"
i was able to get it but i'm getting"yyyymmddhh:mm"
Please give any other answer
Thanks
Arun

declare @mydate datetime

select  @mydate = getdate()

select datename(yy,@mydate)+rtrim(convert(char,datepart(mm,@mydate)))+datename(dd,@mydate)+datename(hh,@mydate)+datename(mi,@mydate)

I got this output : Pl check again (I am using Sybase 11.9.2)

20001131941

Whats ur database

Amit

My database is 11.0.3.
In your answer there seems to be a zero missing from the
month... what happens if you execute the query at 9.04 AM?
(or any other time including zero's?)

Once Again , i'm saying that I am able to get "yyyymmddhh:mm" with the query
--"Select Convert(char(8),getdate(),112) + Convert(char(5),getdate(),108)"--
The only problem is of ":"(colon in between hours & minutes).
Please suggest any other query.
Regarding Amitpagarwal's answer, first of all i'm getting a lot of space after month field & also as said by ahoor,it does not solve the problem of dates having one digit in month , date & time fields.
It is necessary for me to get these fields in only in one format(means no. of digits), irrespective of whether month, date etc.  consists of one digit or multiple digits, since i'm reading the data from a Flat file.
Thanks
Arun

I have got one more answer , it is one liner & is also working perfectly.
"select convert(char(8), getdate(), 112) + convert(char(2), getdate(), 108) + substring(convert(char(5), getdate(),108),4,5)"


But
Anyway, Thanks to everybody for prviding me answer earlier.
Arun Goel