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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to include Time Zone
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 01/29/2013 :  22:44:39  Show Profile  Reply with Quote
Hi Guys,

I have an scrpt that i need to include the timezone.
can you please help me guys on how to do this. thanks.


--Declare Date Parameter
DECLARE @fromDateTime as datetime, @toDateTime as datetime, @timezoneOffset INT,
	@sdate varchar(10), @stime varchar(15), @edate varchar(10), @etime varchar(15)
	set @sdate = '1/1/2013'
	set @stime = '12:00 AM'
	set @edate = '1/29/2013'
	set @etime = '12:00 AM'
	set @timezoneOffset =8
	SET @fromdatetime = convert(datetime,@sdate + ' ' + @stime)
	SET @todatetime = convert(datetime,@edate + ' ' + @etime)
--Script
Select * from #Sample
Where pt.ASURAFDATETIME BETWEEN @fromdatetime AND @toDateTime

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/29/2013 :  23:38:23  Show Profile  Reply with Quote
try to pass values in unambiguos date formats

see

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

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

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 01/30/2013 :  01:48:21  Show Profile  Reply with Quote
Thanks Visakh,

Here is what i do and its working on my script.

DECLARE @fromDateTime as datetime, @toDateTime as datetime,
        @fromTime as datetime, @toTime as datetime,
		@sdate varchar(10), @stime varchar(15), @edate varchar(10), @etime varchar(15),
		@timezoneoffset int
		set @sdate = '1/1/2013'
		set @stime = '12:00 AM'
		set @edate = '1/29/2013'
		set @etime = '12:00 AM'
		set @timezoneoffset=8
		SET @fromdatetime=dbo.getCombinedDateTime(@sdate,@stime)
		SET @toDateTime=dbo.getCombinedDateTime(@edate,@etime)
		SET @fromtime=dbo.getTimezoneOffset(@fromdatetime,@timezoneoffset)
		SET @toTime=dbo.getTimezoneOffset(@todatetime,@timezoneoffset)


This is the result: 2012-12-31 16:00:00.000 2013-01-28 16:00:00.000

Btw, I have another Query which is related on this post.
need to sum up the requiredqty by lineid. my query could not get the exact result. thanks.


drop table #Data

Create table #Data
(LineID nvarchar(10),
model nvarchar(20),
partno nvarchar(35),
requiredqty int,
newqty int,
recqty int,
refqty int);
GO
insert into #Data values('Line1', 'DRDRZR2BLK', 'MOT2775', 1981,48,140,0)
insert into #Data values('Line1', 'DRDRZR2BLK', 'MOT2978', 1981,546,527,0)
insert into #Data values('Line1', 'DRDRZR2BLK', 'MOT2949', 1981,635,0,0)
insert into #Data values('Line11','DRDRZR2BLK', 'MOT2948', 875,863,0,0)
insert into #Data values('Line11','DRDRZR2BLK', 'MOT2956', 875,410,71,0)
insert into #Data values('Line11','DRDRZR2BLK', 'MOT3361', 875,848,0,0)
insert into #Data values('Line2', 'DRDRZR2BLK', 'MOT2938', 1441,1443,0,0)
insert into #Data values('Line2', 'DRDRZR2BLK', 'MOT2952', 1441,391,1013,0)
insert into #Data values('Line2', 'DRDRZR2BLK', 'MOT2968', 1441,29,1305,0)
insert into #Data values('Line4', 'DRDRZR2BLK', 'MOT2938', 27,27,0,0)
insert into #Data values('Line4', 'DRDRZR2BLK', 'MOT2952', 27,34,0,0)
insert into #Data values('Line4', 'DRDRZR2BLK', 'MOT2953', 27,1,0,0)
insert into #Data values('Line3', 'DRDRZR2BLK', 'MOT2942', 1394,0,1383,0)
insert into #Data values('Line3', 'DRDRZR2BLK', 'MOT2943', 1394,535,0,0)
insert into #Data values('Line3', 'DRDRZR2BLK', 'MOT2975', 1394,1,0,0)
insert into #Data values('Rework','DRDRZR2BLK', 'MOT2948', 180,194,0,0)
insert into #Data values('Rework','DRDRZR2BLK', 'MOT2951', 180,187,0,0)
insert into #Data values('Rework','DRDRZR2BLK', 'MOT2775', 180,0,27,0)
Select * from #Data

Select
	d.model,
	d.partno,
	Produce.finphones,
	SUM(REQUIREDQTY) as REQUIREDQTY,
    SUM(Case When (REQUIREDQTY)-(NEWQTY+RECQTY+REFQTY) >0 then (REQUIREDQTY)-(NEWQTY+RECQTY+REFQTY) else 0 end) as CorQty,
	SUM(NEWQTY) as NEWQTY,
	SUM(RECQTY) as RECQTY,
	SUM(REFQTY) as REFQTY
From #Data d
Left Outer Join (Select distinct model, SUM(requiredqty) as finphones
				 From #Data group by model) Produce
On d.model=Produce.model				 	
Group by d.model, d.partno, Produce.finphones
Order by d.partno


RESULT
---------------------------------------------
MODEL-----PARTNO--
---------------------------------------------

Model-------partNo--REQUIREDQTY--CorQty--NEWQTY--RECQTY--REFQTY
DRDRZR2BLK--MOT2775---5898------1793--------48---140----	0
DRDRZR2BLK--MOT2938---5898-------0--------1470---0------	0
DRDRZR2BLK--MOT2942---5898-------11--------0----1383----	0
DRDRZR2BLK--MOT2943---5898-------859------535---0-------	0
DRDRZR2BLK--MOT2948---5898-------12-------1057--0-------	0
DRDRZR2BLK--MOT2949---5898-------1346-----635----0-------	0
DRDRZR2BLK--MOT2951---5898-------0--------187----0------	0
DRDRZR2BLK--MOT2952---5898-------37-------425----1013---	0
DRDRZR2BLK--MOT2953---5898-------26-------1------0------	0
DRDRZR2BLK--MOT2956---5898-------394------410----71------	0
DRDRZR2BLK--MOT2968---5898-------107------29-----1305----	0
DRDRZR2BLK--MOT2969---5898--------153-----0------27------	0
DRDRZR2BLK--MOT2975---5898--------1393-----1-----0------	0
DRDRZR2BLK--MOT2978---5898--------908------546---527-----	0
DRDRZR2BLK--MOT3361---5898--------27-------848---0------	0






Edited by - Villanuev on 01/30/2013 01:56:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/30/2013 :  03:13:27  Show Profile  Reply with Quote
so whats your expected result?

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

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 01/30/2013 :  03:28:38  Show Profile  Reply with Quote
Thanks Visakh,

THis is the expected result:

RESULT

Model-------PartNo--REQUIREDQTY--CorQty--NEWQTY--RECQTY--REFQTY
---------------------------------------------------------------
DRDRZR2BLK--MOT2775---5898------1793--------48---140----	0
DRDRZR2BLK--MOT2938---5898-------0--------1470---0------	0
DRDRZR2BLK--MOT2942---5898-------11--------0----1383----	0
DRDRZR2BLK--MOT2943---5898-------859------535---0-------	0
DRDRZR2BLK--MOT2948---5898-------12-------1057--0-------	0
DRDRZR2BLK--MOT2949---5898-------1346-----635----0-------	0
DRDRZR2BLK--MOT2951---5898-------0--------187----0------	0
DRDRZR2BLK--MOT2952---5898-------37-------425----1013---	0
DRDRZR2BLK--MOT2953---5898-------26-------1------0------	0
DRDRZR2BLK--MOT2956---5898-------394------410----71------	0
DRDRZR2BLK--MOT2968---5898-------107------29-----1305----	0
DRDRZR2BLK--MOT2969---5898--------153-----0------27------	0
DRDRZR2BLK--MOT2975---5898--------1393-----1-----0------	0
DRDRZR2BLK--MOT2978---5898--------908------546---527-----	0
DRDRZR2BLK--MOT3361---5898--------27-------848---0------	0


---this is the total of model per lineid
model LineID requiredqty
DRDRZR2BLK Line1 1981
DRDRZR2BLK Line11 875
DRDRZR2BLK Line2 1441
DRDRZR2BLK Line3 1394
DRDRZR2BLK Line4 27
DRDRZR2BLK Rework 180
--------------------------------
-------------------------5898--need to put this value in
the requiredqty column

Edited by - Villanuev on 01/30/2013 04:16:17
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000