Date Problem

How can we get in a date in format of type
yyyymmddhhmm?

I have tried this query
"Select convert(char(20),<col-name>, <style>) from <table-name>
go"
 styles: 112,100,109,101,13,14,15.
all these styles i have tried but didn't get the required result(yyyymmddhhmm).
goel_arAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ahoorCommented:
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
0
amitpagarwalCommented:

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
0
ahoorCommented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

goel_arAuthor Commented:
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
0
amitpagarwalCommented:
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
0
ahoorCommented:
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?)
0
goel_arAuthor Commented:
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
0
albertspCommented:
Try this one:

set nocount on

declare @datum char(12)
declare @mi    varchar(2)
declare @hh    varchar(2)
declare @date  datetime

select @datum="000000000000"
select @date=getdate()

select @mi=convert(char,datepart(mi,@date))
select @hh=convert(char,datepart(hh,@date))

select @datum=stuff(@datum,1,8,convert(char,@date,112))
select @datum=stuff(@datum,11-char_length(@hh),char_length(@hh),@hh)
select @datum=stuff(@datum,13-char_length(@mi),char_length(@mi),@mi)

select @datum



Not a one liner but it works!!!

Succes, Albert
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
goel_arAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.