What is the shortcut function to get current date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

What is the shortcut function to get current date

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-06-27 : 21:44:57

Hello,

Because I need to retrieve a data that from a year ago, so I can use WHERE year_and_period = 201106. However I don't want to change the period every month to get a new data from last year. Therefore, is there anyone know the way that can retrieve the data for last year by using function as (current date- 365 days) so that it is going to update automatically without changing the period every month.

Thanks

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-27 : 21:53:02

You didn't provide any detail about your database or your queries, but here's a basic idea of how you could do this. Also, it will depend on which version of SQL Server you're using as the "date" data type is available in MS SQL Server 2008 and later:

declare @table table (sdate date)
insert into @table (sdate)
values
('2011-06-26'),
('2011-06-27'),
('2011-06-28'),
('2011-06-29')

select sdate
from @table
where sdate=DATEADD(dd, -365, convert(date, getdate()))

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-06-27 : 23:01:44

I apologize for not saying it in detail. In fact this is what I'm working on :
(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN '201203' AND '201205' THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 3 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN '201112' AND '201205' THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 6 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN '201104' AND '201205' THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 12 months usage'

As you can see I used the BETWEEN function to retrieve the data from last 3, 6 and 12 months period. So if I want to update this in the future I have to go back and change the year_period like between '201204 and 201206'. Is there a way that I can use as (current day - 365 days) to get a 12 months usage instead of BETWEEN 201104 and 201205. When we figure that out, we can simply apply for any periods the last 3 or 6 months.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-27 : 23:23:16

This is going to be ugly and if it's at all possible for you to redesign what I can assume to be your "views" so that you're using dates rather than a 6 character representation of a yyyymm, this would probably be a bit cleaner...but (I'd at the very least try to get the yyyymm into an integer so you know it will order as a number)

declare @LastMonth char(6)
,@ThreeMonthsAgo char(6)
,@SixMonthsAgo char(6)
,@TwelveMonthsAgo char(6)

select @LastMonth =LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6)
,@ThreeMonthsAgo =LEFT(CONVERT(CHAR(8), DATEADD(MM, -3, getdate()),112),6)
,@SixMonthsAgo =LEFT(CONVERT(CHAR(8), DATEADD(MM, -6, getdate()),112),6)
,@TwelveMonthsAgo=LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112),6)

(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN @ThreeMonthsAgo AND @LastMonth THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 3 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN @SixMonthsAgo AND @LastMonth THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 6 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN @TwelveMonthsAgo AND @LastMonth THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 12 months usage'

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-06-27 : 23:27:42

Thank you for the help.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-27 : 23:31:07

welcome

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-06-28 : 10:25:37

Hi,

I tried the query today at work and it showed a error with the function as : "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

Do you know how to fix it?

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-28 : 11:09:52

quote:
Originally posted by tantcu

Hi,

I tried the query today at work and it showed a error with the function as : "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

Do you know how to fix it?

hi,

i have tested the script too, it is working properly.

Try to print your declared variables too

declare @LastMonth char(6)
,@ThreeMonthsAgo char(6)
,@SixMonthsAgo char(6)
,@TwelveMonthsAgo char(6)

select @LastMonth =LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6)
,@ThreeMonthsAgo =LEFT(CONVERT(CHAR(8), DATEADD(MM, -3, getdate()),112),6)
,@SixMonthsAgo =LEFT(CONVERT(CHAR(8), DATEADD(MM, -6, getdate()),112),6)
,@TwelveMonthsAgo=LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112),6)

print @LastMonth +'==='+ @ThreeMonthsAgo +'==='+@SixMonthsAgo+'==='+@TwelveMonthsAgo

--------------------------
Get rich or die trying
--------------------------

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-29 : 01:10:36

Did you try to replace the "getdate()" in the variable declaration with a column from a table? If you did, that won't work. It would be very helpful if you posted your whole query.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-29 : 01:15:21

Or actually, it looks like you may have tried to incorporate your variable declaration into the select statement itself...which won't work for the reason it returned the error. The idea with declaring the variable before the query is to simplify your code and "state the meaning of the variable" once rather than writing it multiple times in your code. If you can't do that, simply insert the definitions of the declared variables into your query itself and get rid of the declaration. EX:

(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -3, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 3 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -6, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 6 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 12 months usage'

Just makes for really long, difficult to read code.

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-06-29 : 10:10:36

Thanks for all your help. yeah I tried to get rid of the declaration and include the very long code as you wrote above and it works.

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-06-29 : 10:17:44

Hey guys,

I encounter another difficulty in the same database that I'm working on. After getting the current date formate right.Now my boss asked me to compare the quantity on hand as ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) as 'total_qty') to the last 3, 6 , 12 months usage and classified their status as
good if quantity on hand <= 90 day supply.(90 day supply means 6 months usage)
Overstock if quantity on hand > 90 days supply and <= 365 days
NNU = 120 days
Surplus > 365 days supply
Hardcode = No usage 12 months.

What function should I use in SQL to be able to do this?
This is what I'm currently working on.

SELECT
[P21].[dbo].[p21_view_inventory_value_report].[default_branch_id]
,[P21].[dbo].[p21_view_inventory_value_report].[branch_description]
,[P21].[dbo].p21_view_inventory_value_report.[item_id]
,[P21].[dbo].[p21_view_inventory_value_report].[item_desc]
,([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) as 'total_qty'
,(([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty)-[P21].[dbo].p21_view_inv_loc.qty_allocated) AS 'total_available'
,[P21].[dbo].[p21_view_inventory_value_report].[cost]
,((([P21].[dbo].p21_view_inventory_value_report.qty_on_hand - [P21].[dbo].p21_view_inventory_value_report.special_layer_qty) * [P21].[dbo].[p21_view_inventory_value_report].[cost]) + [P21].[dbo].p21_view_inventory_value_report.special_layer_qty) AS extended_value
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -3, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 3 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -6, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6)THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 6 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6)THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 12 months usage'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources