How to convert DATEADD(HH,-12,GETDATE()) result to 'yyyymmddhh'?

I have Integer data type column name Dimdatetimeid in my table. It has date and time info like 2009042310, 2009042322 etc. Now I want to get some info 12 hrs back onwards.
So I try to write the query
Select * from table1 where dimdatetimeid >= DATEADD(HH,-12,GETDATE()) like that.
The problem is DATEADD(HH,-12,GETDATE()) will give 2009-04-22 23:20:46.400
But I want to convert it as 2009042223. Please let me know. Thanks.
PKTGAsked:
Who is Participating?
 
Patrick MatthewsCommented:
Sorry, typo:

SELECT *
FROM Table1
WHERE dimdatetimeid >= CONVERT(int, CONVERT(varchar, DATEADD(hh, -12, GETDATE()), 112) +
      LEFT(CONVERT(varchar, DATEADD(hh, -12, GETDATE()), 108), 2))
0
 
RiteshShahCommented:
select convert(varchar,getdate(),112)

more list is given at http://www.sqlhub.com/
0
 
Nathan RileyFounderCommented:
select convert(char(13),dateadd(hh,-12,getdate()),120)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Patrick MatthewsCommented:
Hello PKTG,

Your basic question has been answered.  That said, I think you should think about using a datetime or
smalldatetime column instead.  Always use the right data type for the job :)

Regards,

Patrick
0
 
PKTGAuthor Commented:
I am comparing with integer column value so it should be  YYYYMMDDHH Format. Thanks
0
 
PKTGAuthor Commented:
I understand using integer column for date is not good one. But in my client place they are using like that. So i have to live with that :)
0
 
RiteshShahCommented:
you can use this one also

select convert(varchar,getdate(),112) + left(convert(varchar,getdate(),24),2)
0
 
Patrick MatthewsCommented:
SELECT *
FROM Table1
WHERE dimdatetimeid >= CONVERT(int, CONVERT(varchar, GETDATE(), 112) + LEFT(CONVERT(varchar, GETDATE(), 108), 2))
0
 
PKTGAuthor Commented:
Please i need to get data from 12hrs back onwards.
0
 
Patrick MatthewsCommented:
PKTG said:
>>Please i need to get data from 12hrs back onwards.

Right, so please see my corrected post.
0
 
RiteshShahCommented:
isn't this working? select convert(varchar,getdate(),112) + left(convert(varchar,getdate(),24),2)
0
 
PKTGAuthor Commented:
Nope. If i run your above query it will give the result as 2009042312. But i want 2009042300. Then only i will get data from 12 hours bak onwards.
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.