Universe – Various date formats

Posted: May 17, 2011 in Business Objects, SQL Server, Universe
Tags: , ,

While building the Business Objects Universe  we often need some generic date objects like Today, Tomorrow, Yesterday, Current Month and so on. Here are the T-SQL statements for the most common of these objects –

Today – Select dateadd(d,DATEDIFF(d,0,getdate()),0)

Tomorrow – Select dateadd(d,DATEDIFF(d,0,getdate()),0)+1

Yesterday – Select dateadd(d,DATEDIFF(d,0,getdate()),0)-1

Current Month – Select left(CONVERT(VARCHAR(10), getdate(), 112),6)

Previous Month – Select left(CONVERT(VARCHAR(10), dateadd(m,-1,getdate()), 112),6)

Next Month – Select left(CONVERT(VARCHAR(10), dateadd(m,1,getdate()), 112),6)

Current Month Start Date – Select dateadd(m,datediff(m,0,getdate()),0)

Current Month End Date – Select dateadd(m,datediff(m,0,getdate())+1,0)-1

Current Year – Select year(getdate())

Next Year – Select Year(dateadd(year,1,getdate()))

Previous Year – select Year(dateadd(year,-1,getdate()))

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s