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)
 How to include Time Zone

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-01-29 : 22:44:39
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

52326 Posts

Posted - 2013-01-29 : 23:38:23
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 - 2013-01-30 : 01:48:21
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





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-30 : 03:13:27
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 - 2013-01-30 : 03:28:38
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
Go to Top of Page
   

- Advertisement -