SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

441 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
52317 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

441 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
52317 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

441 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  
 New 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 1.33 seconds. Powered By: Snitz Forums 2000