Date formatting - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Date formatting

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-10-10 : 23:15:20

Hi Guys,

I have date range parameter that requires 3 months coverage. dates depends on what records the user would like to pull out.
Just created 3 query separated by union all and use the date parameter to pull out records
and combine into one result set. I could not format the date that i wanted to be. it start only 2012/07/01 and the ending is 2012/07/01.
Need your help guys. your reply is very much appreciated. Thanks.


sample
Declare @strtable as table(tranmonth datetime,
		Model nvarchar(35),
		Partno nvarchar(35),
		ProduceQty int, 
		CoresQty int,
		NewPartsQty int,
		RequiredQty int,
		CUsage numeric(28,12),
		OUsage numeric(28,12),
		RUsage numeric(28,12))  
insert into @strtable values('2012-07-01','INCREDIBLE','JOV1648',3585,6535,635,7170,0.91,0.09,0.0)
insert into @strtable values('2012-08-01','INCREDIBLE','JOV1648',3083,5248,918,6166,0.85,0.15,0.0)
insert into @strtable values('2012-09-01','INCREDIBLE','JOV1648',2354,1407,993,2400,0.59,0.41,0.0)

Select * from @strtable

;With Cte as
( Select a.*, lflag=1 from @strtable a
  Where tranmonth between('2012-07-01 12:00AM') and ('2012-07-31 12:00PM')
  
  --Select DATEADD(mm, DATEDIFF(mm, 0,@StartDate), 0),
 --DATEADD(mm, DATEDIFF(mm, 0, @StartDate) + 0, 0) --2012-07-01
  
  Union all
  
  Select b.*, lflag=2 from @strtable b
  Where tranmonth between('2012-08-01 12:00AM') and ('2012-08-31 12:00PM')
  
  --Select DATEADD(mm, DATEDIFF(mm, 0,DateADd(mm,+1,@StartDate)), 0),
  --DATEADD(mm, DATEDIFF(mm, 0, DateADd(mm,+1,@StartDate)) + 0, 0) --2012-08-01 
  
  Union all
  Select c.*, lflag=1 from @strtable c
  Where tranmonth between('2012-09-01 12:00AM') and ('2012-09-30 12:00PM')
  
  --Select DATEADD(mm, DATEDIFF(mm, 0,DateADd(mm,+2,@StartDate)), 0),
  --DATEADD(mm, DATEDIFF(mm, 0, DateADd(mm,+2,@StartDate)) + 0, 0)  --2012-09-30
)
Select * from cte

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-11 : 00:07:55

are you asking on formatting the date for displaying on reports? whats the format you're looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-10-11 : 02:33:05

Hi Visakh,

Just want to clear. in my SSRS report i have date and time parameter.
user will choose 3 months data coverage based on date range i choose.

[code]
Declare @FromDateTime datetime, @ToDateTime Datetime,
@FromTime nvarchar(8), @toTime nvarchar(8)

Set @FromDateTime='2012-07-01'
Set @ToDateTime='2012-09-30'
Set @FromTime='12:00 AM'
Set @ToTime='12:00 AM'

----

On my Query, I create CTE with 3 select statement that use date range on each query based on
the parameter.

; WITH CTE AS
(
--First Query
Select * from @strtable
Where tranmonth between ??? (should be ('2012-07-01 12:00 AM') and ( '2012-07-31 12:00 AM')

Union all

Select * from @strtable
Where tranmonth between ??? (should be ('2012-08-01 12:00 AM') and ( '2012-08-31 12:00 AM')

Union all

Select * from @strtable
Where tranmonth between ??? (should be ('2012-09-01 12:00 AM') and ( '2012-09-30 12:00 AM')
)
Select * from CTE
[code/]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-10-11 : 03:02:52

Hi Visakh,

Here is my Sample. the only difference is that i have only one date parameter.
also how could i incorporate the 12:00 AM.
these capture the 3 months coverage for 3 squery.

[code]

Declare @strtable as table(tranmonth datetime,
Model nvarchar(35),
Partno nvarchar(35),
ProduceQty int,
CoresQty int,
NewPartsQty int,
RequiredQty int,
CUsage numeric(28,12),
OUsage numeric(28,12),
RUsage numeric(28,12))
insert into @strtable values('2012-07-01','INCREDIBLE','JOV1648',3585,6535,635,7170,0.91,0.09,0.0)
insert into @strtable values('2012-08-01','INCREDIBLE','JOV1648',3083,5248,918,6166,0.85,0.15,0.0)
insert into @strtable values('2012-09-01','INCREDIBLE','JOV1648',2354,1407,993,2400,0.59,0.41,0.0)

Declare @Startdate datetime
Set @Startdate='2012/07/09'

; WITH CTE AS
(
--First Query (First Month)
Select * from @strtable
Where tranmonth between DATEADD(mm, DATEDIFF(mm, 0,@StartDate), 0)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Startdate))),DATEADD(mm,1,@Startdate)),101)

Union all

--Second Query (Second Month)
Select * from @strtable
Where tranmonth between DATEADD(mm, DATEDIFF(mm, 0, DateADd(mm,+1,@StartDate)) + 0, 0)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,2,@Startdate))),DATEADD(mm,2,@Startdate)),101)

Union all

--Thirst Query ( Third Month)
Select * from @strtable
Where tranmonth between DATEADD(mm, DATEDIFF(mm, 0, DateADd(mm,+2,@StartDate)) + 0, 0)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,3,@Startdate))),DATEADD(mm,3,@Startdate)),101)
)
Select * from CTE
[code/]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-11 : 15:44:19

you dont need three selects

you can apply grouping based on month dates and then do required aggregation

like


Select MONTH(DATEADD(mm, DATEDIFF(mm, 0,DateField), 0)) AS MonthValue,
AVG(column1),SUM(column2),... similar aggregations on other columns depending on requirement
from @strtable 
Where tranmonth >=DATEADD(mm, DATEDIFF(mm, 0,@StartDate), 0) 
and tranmonth <DATEADD(mm,DATEDIFF(mm,0,@startDate)+3,0)-1
GROUP BY DATEADD(mm, DATEDIFF(mm, 0,DateField), 0)

see this to understand the logic

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-10-14 : 21:50:15

THank you very much visakh..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-15 : 10:39:49

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources