• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 755
  • Last Modified:

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).
0
goel_ar
Asked:
goel_ar
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now