Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-04-23
12
Medium Priority
?
1,235 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:PKTG
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24216929
select convert(varchar,getdate(),112)

more list is given at http://www.sqlhub.com/
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24216944
select convert(char(13),dateadd(hh,-12,getdate()),120)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24217020
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:PKTG
ID: 24217035
I am comparing with integer column value so it should be  YYYYMMDDHH Format. Thanks
0
 

Author Comment

by:PKTG
ID: 24217059
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24217075
you can use this one also

select convert(varchar,getdate(),112) + left(convert(varchar,getdate(),24),2)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24217085
SELECT *
FROM Table1
WHERE dimdatetimeid >= CONVERT(int, CONVERT(varchar, GETDATE(), 112) + LEFT(CONVERT(varchar, GETDATE(), 108), 2))
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24217101
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
 

Author Comment

by:PKTG
ID: 24217149
Please i need to get data from 12hrs back onwards.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24217173
PKTG said:
>>Please i need to get data from 12hrs back onwards.

Right, so please see my corrected post.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24217188
isn't this working? select convert(varchar,getdate(),112) + left(convert(varchar,getdate(),24),2)
0
 

Author Comment

by:PKTG
ID: 24217368
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question