updating dates, day, quaters, motnhs in a tbale

Question

Hi  guys,

i Need to update the table dates table till current date. can anyone help me in writing query pls i am new to sql

Day	Month	Quarter	Fyear	Year
01/04/2009 00:00	Apr-09	Q1 0910	910	2009
02/04/2009 00:00	Apr-09	Q1 0910	910	2009
03/04/2009 00:00	Apr-09	Q1 0910	910	2009
04/04/2009 00:00	Apr-09	Q1 0910	910	2009
05/04/2009 00:00	Apr-09	Q1 0910	910	2009
06/04/2009 00:00	Apr-09	Q1 0910	910	2009
07/04/2009 00:00	Apr-09	Q1 0910	910	2009
08/04/2009 00:00	Apr-09	Q1 0910	910	2009
09/04/2009 00:00	Apr-09	Q1 0910	910	2009
10/04/2009 00:00	Apr-09	Q1 0910	910	2009

here is sample data.

thanks

ravilla

Answer 1

update table1
set datetime_field = getdate(), -- complete datetime
date_field = datepart(d, getdate()), --date
month_field = datepart(month, getdate()),  --month
year_field = datepart(year, getdate()) -- year

if there is where clause needed, you can use that.

regards

joon

Answer 2

Try

select day1, replace(substring(CONVERT(varchar,day1,106),4,8),' ','-') as Month,
case when cast(substring(CONVERT(varchar,day1,112),1,4) as decimal(4)) between 4 and 6 then 'Q1'
case when cast(substring(CONVERT(varchar,day1,112),1,4) as decimal(4)) between 7 and 9 then 'Q2'
case when cast(substring(CONVERT(varchar,day1,112),1,4) as decimal(4)) between 10 and 12 then 'Q3'
case when cast(substring(CONVERT(varchar,day1,112),1,4) as decimal(4)) between 1 and 3 then 'Q4' end as Quarter,
substring(CONVERT(varchar,day1,112),1,4) as Year
from tab1;

---

Many Thanks & Best Regards, Hua Min

Answer 3

Hi joon

I need the insert the dates 10/04/2009 to 31/04/2013 so my table should be in the in the above formart with quarters as well in as well.

kind regards

ravila

Answer 4

Hi  guys,

i Need to insert the data into table with all dates in it till 2013-03-31 that is fincail year end. for example in my table i got my table updated till 2011-04-01 to 2012-03-31 now i need to insert another fincaial year from 201-04-01 to 2013-03-31 can anyone help me in writing query pls i am new to sql it very urgent

Day	Month	Quarter	Fyear	Year
01/04/2009 00:00	Apr-09	Q1 0910	910	2009
02/04/2009 00:00	Apr-09	Q1 0910	910	2009
03/04/2009 00:00	Apr-09	Q1 0910	910	2009
04/04/2009 00:00	Apr-09	Q1 0910	910	2009
05/04/2009 00:00	Apr-09	Q1 0910	910	2009
06/04/2009 00:00	Apr-09	Q1 0910	910	2009
07/04/2009 00:00	Apr-09	Q1 0910	910	2009
08/04/2009 00:00	Apr-09	Q1 0910	910	2009
09/04/2009 00:00	Apr-09	Q1 0910	910	2009
10/04/2009 00:00	Apr-09	Q1 0910	910	2009

here is sample data.

thanks

ravilla

Answer 5

Here is the query to populate similar table. Just verify the column names and modify the query accordingly and you are done. Make sure to test it properly before freezing it.

declare @startDate as datetime,@enddate as datetime
--initialize
set @startDate = '04/01/2012'
set @enddate = '03/01/2013'

while @startDate<@enddate
begin
insert into <YourTableName>(
Date
,MonthOfYear
,[Quarter]
,[Year]
,[MonthName]
,QuarterName)
select cast(convert(varchar(8),@startdate,112) as int)
,Month(@startdate)
,datename(quarter,@startdate) as Quarter
,Year(@startdate)
,CASE datename(quarter,@startdate)
WHEN 1 THEN 'First Quarter'
WHEN 2 THEN 'Second Quarter'
WHEN 3 THEN 'Third Quarter'
WHEN 4 THEN 'Fourth Quarter'
END AS QuarterName

set @startDate = @startDate +1
end
GO

---

HTH,
Cheers!!
Ashish
Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

Answer 6

Can you tell me how '0910' is derived? Based on which part of the date?

---

Many Thanks & Best Regards, Hua Min

Answer 7

Try

WITH each_year AS
   (select CONVERT(datetime,'20090401',112) as day1, replace(substring(CONVERT(varchar,CONVERT(datetime,'20090401',112),106),4,8),' ','-') as Month,
case when cast(substring(CONVERT(varchar,CONVERT(datetime,'20090401',112),112),1,4) as decimal(4)) between 4 and 6 then 'Q1'
when cast(substring(CONVERT(varchar,CONVERT(datetime,'20090401',112),112),1,4) as decimal(4)) between 7 and 9 then 'Q2'
when cast(substring(CONVERT(varchar,CONVERT(datetime,'20090401',112),112),1,4) as decimal(4)) between 10 and 12 then 'Q3'
when cast(substring(CONVERT(varchar,CONVERT(datetime,'20090401',112),112),1,4) as decimal(4)) between 1 and 3 then 'Q4' end as Quarter,
substring(CONVERT(varchar,CONVERT(datetime,'20090401',112),112),1,4) as Year
    UNION ALL
    select day1+1, replace(substring(CONVERT(varchar,day1+1,106),4,8),' ','-') as Month,
    case when cast(substring(CONVERT(varchar,day1+1,112),1,4) as decimal(4)) between 4 and 6 then 'Q1'
    when cast(substring(CONVERT(varchar,day1+1,112),1,4) as decimal(4)) between 7 and 9 then 'Q2'
    when cast(substring(CONVERT(varchar,day1+1,112),1,4) as decimal(4)) between 10 and 12 then 'Q3'
    when cast(substring(CONVERT(varchar,day1+1,112),1,4) as decimal(4)) between 1 and 3 then 'Q4' end as Quarter,
    substring(CONVERT(varchar,day1+1,112),1,4) as Year
    FROM each_year
    WHERE CONVERT(varchar,day1,112) <= '20130331')
SELECT *
FROM each_year;
go

Many Thanks & Best Regards, Hua Min

Answer 8

Asuming that 2009 = 910 and 210 = 1300... SQL Server can only store dates going ack to 1/1/1753, so you're going to be limited to Fyear = 453 (aka 1753)

So... based on that, the following script should work for you.

-- This is just a temp table for use with this example --
IF OBJECT_ID('tempdb..#Cal') IS NOT NULL DROP TABLE #Cal
CREATE TABLE #Cal (
	[Day] DATETIME,
	[Month] AS LEFT(DATENAME(mm, [Day]), 3) + '-' + RIGHT(DATEPART(yyyy, [Day]), 2),
	[Quarter] CHAR(7),
	[Fyear] INT,
	[Year] AS DATEPART(yyyy, [Day]))
	
-- The actual solution --
	
DECLARE 
	@d DATETIME = '17530401',
	@fy INT = 453
WHILE @d <= '31220331'
BEGIN 
	INSERT INTO #Cal ([Day], [Quarter], [Fyear]) 
		VALUES (
			@d, 
			CASE 
				WHEN DATEPART(mm, @d) IN (4,5,6) THEN 'Q1 ' + RIGHT('0' + CAST(@fy AS VARCHAR(4)), 4)
				WHEN DATEPART(mm, @d) IN (7,8,9) THEN 'Q2 ' + RIGHT('0' + CAST(@fy AS VARCHAR(4)), 4)
				WHEN DATEPART(mm, @d) IN (10,11,12) THEN 'Q3 ' + RIGHT('0' + CAST(@fy AS VARCHAR(4)), 4)
				WHEN DATEPART(mm, @d) IN (1,2,3) THEN 'Q4 ' + RIGHT('0' + CAST(@fy AS VARCHAR(4)), 4)
			END,
			@fy)
	SET @fy = CASE 
				WHEN DATEPART(mm, @d) = 4 AND DATEPART(dd, @d) = 31 
				THEN @fy + 1
				ELSE @fy
			END
	SET @d = DATEADD(dd, 1, @d)
END
SELECT * FROM #Cal
			
	

---

Jason Long

Answer 9

Hi Ravilla,
Owing to the issue of "The maximum recursion 100", I suggest you use a loop to insert the records you need by taking the columns I provided to you above, using temp tables.

See this for the way of using a loop
http://msdn.microsoft.com/en-us/library/ms178642.aspx

---

Many Thanks & Best Regards, Hua Min