hi to all - 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 2000 Forums

Transact-SQL (2000)

hi to all

Author

Topic

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2009-06-05 : 23:37:31

hi to all sql forum members.........
in sql server 2000 i need get output only date not time for that which datatype i will put in the coding.....if possible reply me coding.pls....thanks for ur attention......

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-06 : 00:30:40

[code]dateadd(day, datediff(day, 0, datecol), 0)[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-06 : 06:12:36

KH's example brings up the value of datecol with time part is set to zero.
If you mean you want a datatype without time part, that is not possible. Always use datetime datatype.
SQL Server 2008 has different datatypes for date and time.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 04:18:34

quote:
Originally posted by avijit111@gmail.com

hi to all sql forum members.........
in sql server 2000 i need get output only date not time for that which datatype i will put in the coding.....if possible reply me coding.pls....thanks for ur attention......

you can use datetime datatype itself and use expression KH gave to strip off timepart to 00:00:00. And in your front end application you can use date formatting functions to make date display in your relevant format without time at all. storing dates in datetime fields give you flexibility of using date functions like dateadd,datediff,.. which makes your date manipulations much easier.

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2009-06-08 : 01:11:42

quote:
Originally posted by visakh16

quote:
Originally posted by avijit111@gmail.com

hi to all sql forum members.........
in sql server 2000 i need get output only date not time for that which datatype i will put in the coding.....if possible reply me coding.pls....thanks for ur attention......

you can use datetime datatype itself and use expression KH gave to strip off timepart to 00:00:00. And in your front end application you can use date formatting functions to make date display in your relevant format without time at all. storing dates in datetime fields give you flexibility of using date functions like dateadd,datediff,.. which makes your date manipulations much easier.

helo to all sql team.......
please tell me how to code serial numbers (like 1234567 continue..)
if possible send me the coding....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 01:32:51

Hi avijit,

please explain what do you mean by "code serial numbers (like 1234567 continue..)" ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-08 : 02:12:05

quote:
Originally posted by avijit111@gmail.com

quote:
Originally posted by visakh16

quote:
Originally posted by avijit111@gmail.com

hi to all sql forum members.........
in sql server 2000 i need get output only date not time for that which datatype i will put in the coding.....if possible reply me coding.pls....thanks for ur attention......

you can use datetime datatype itself and use expression KH gave to strip off timepart to 00:00:00. And in your front end application you can use date formatting functions to make date display in your relevant format without time at all. storing dates in datetime fields give you flexibility of using date functions like dateadd,datediff,.. which makes your date manipulations much easier.
helo to all sql team.......
please tell me how to code serial numbers (like 1234567 continue..)
if possible send me the coding....

Again, if you want to show data in front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2009-06-08 : 23:26:46

quote:
Originally posted by madhivanan

quote:
Originally posted by avijit111@gmail.com

quote:
Originally posted by visakh16

quote:
Originally posted by avijit111@gmail.com

hi to all sql forum members.........
in sql server 2000 i need get output only date not time for that which datatype i will put in the coding.....if possible reply me coding.pls....thanks for ur attention......

you can use datetime datatype itself and use expression KH gave to strip off timepart to 00:00:00. And in your front end application you can use date formatting functions to make date display in your relevant format without time at all. storing dates in datetime fields give you flexibility of using date functions like dateadd,datediff,.. which makes your date manipulations much easier.
helo to all sql team.......
please tell me how to code serial numbers (like 1234567 continue..)
if possible send me the coding....

Again, if you want to show data in front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

serial number it has been done by me so thats over thankyou for ur greate support...

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-11 : 00:32:42

Hi This is useful for u

http://www.sql-server-helper.com/tips/date-formats.aspx

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2009-06-24 : 04:06:45

quote:
Originally posted by Nageswar9

Hi This is useful for u

http://www.sql-server-helper.com/tips/date-formats.aspx

ALTER PROCEDURE MAINGATE_RM
(
@company_no glcompany,
@location_no gllocn,
@lang_id int,
@date_from datetime,
@date_to datetime
)
as

declare @today datetime, @HEADING varchar(60)

select @today = @date_to
begin

create table #MAINGATE_RM
(
sl_no int,
company GLCOMPANY,
location GLLOCN,
unloading_no varchar(18),
entry_no varchar(3),
material varchar(60),
ul_po_no varchar(18),
ul_date_time varchar(25),
truck varchar(15),
ven_name varchar(100),
mg_entry_check_by varchar(20),
mg_entry_date_time varchar(25),
mg_entry_po varchar(18),
gross_date_time varchar(25),
gross_time_po varchar(18),
tare_date_time varchar(25),
tare_time_po varchar(18),
mg_exit_date_time varchar(25),
mg_exit_po varchar(18),
net_weight numeric(9),
puc varchar(10),
vendor_code cmn_cust_ven_code
)

SELECT @heading = 'MAINGATE REPORT'

insert into #MAINGATE_RM(company,location ,unloading_no,entry_no,material,ul_po_no,ul_date_time,
truck,ven_name,mg_entry_check_by,mg_entry_date_time, mg_entry_po,gross_date_time,gross_time_po,tare_date_time,
tare_time_po,mg_exit_date_time,mg_exit_po,net_weight,puc,vendor_code )

select a.company,a.location,a.unloading_no,a.entry_no,a.material,a.ul_po_no,CONVERT(DATETIME,a.ul_date_time,108),a.truck,
a.ven_name,a.mg_entry_check_by,CONVERT(DATETIME,a.mg_entry_date_time,103),a.mg_entry_po,
CONVERT(DATETIME,a.gross_date_time,103),a.gross_time_po,CONVERT(DATETIME,a.tare_date_time,103),
a.tare_time_po,CONVERT(DATETIME,a.mg_exit_date_time,103),a.mg_exit_po,b.net_weight,a.puc,C.vendor_code

from main_gate_rm_entry_table a inner join weightment_entry_rm b OUTER JOIN pur_po_header C
on a.unloading_no = b.unloading_no
where a.company = @company_no
and a.entry_no = b.entry_no
and convert(datetime,(rtrim(substring(b.tare_time,1,11))),103) between @date_from AND @date_to
order by ul_po_no

declare @row_no int,
@tot_row int

select @tot_row = (select count(*) from #MAINGATE_RM)
select @row_no = 1
set rowcount 1

while (@row_no<= @tot_row)
begin
update #MAINGATE_RM
set sl_no = @row_no
where isnull(sl_no,0) = 0
select @row_no = @row_no + 1
end
set rowcount 0
select *,@date_from[FROM_DATE], @date_to[to_DATE],@HEADING[HEADING] from #MAINGATE_RM

END

THE C TABLE I NEED TO ATTACHED WITH a and b BUT HAVING MATCHING ONLY COMPANY_NO AND LOCATION_NO .......PLS HELP TO FIND THE SOLUTION......

ok i will send u the data.....

company
locno
key_value
unloading_no
entry_no
trans_code
challan_no
challan_qty
gross_weight
tare_weight
trans_name
gross_time
tare_time
weight_no
net_weight
chalan_date
chalan_time
SELECTION

above is the table from b and below from a.

company
location
key_value
unloading_no
entry_no
ul_po_no
ul_date_time
ven_code
ven_name
truck
material
mg_entry_check_by
mg_entry_date_time
mg_entry_po
gross_date_time
gross_time_po
tare_date_time
tare_time_po
mg_exit_check_by
mg_exit_date_time
mg_exit_po
puc

and below from c---

company_code
locn_no
po_no
amend_no
amend_date
amend_reference
amend_desc
ack_no
ack_date
ack_remarks
po_date
po_type
po_category
po_mode
buyer_code
module_id
po_status
onetime_vendor_flag
po_close_date
vendor_code
payto_vendor
pay_term_no
advance_payable
advance_paid
schedule_flag
delivery_date
shipment_flag
gi_flag
quality_indicator
base_curr_value
po_value
po_addnl_value
po_total_value
tr_currency_code
tr_exchange_rate
quot_no
reason
payment_mode
ins_liability
ins_term
ins_amount
valid_from
valid_to
allocated_amount
remaining_amount
lc_applicable_flag
last_nego_date
last_ship_date
trans_ship_flag
part_ship_flag
authorise_user_name
authorise_date
prev_po_status
ap_locn_no
fob_point
where_used
payment_flag
freeze_date
inco_terms
fs_fund_number
asset_locn
asset_id
cc_allocate_flag
remarks
create_locn_no
created_date
user_id
modified_date
modified_user_id
timestamp
vat_desirable
vat_inclusive
vat_category
vat_class

now i think u could do something for me......pls how i can join table a,b,and c...outer join issue....

Kart
Starting Member

2 Posts

Posted - 2009-06-25 : 03:27:22

For date selection you can convert(char(12),getdate(),101)

take life as its comes leaves as its goes

os_yadav
Starting Member

4 Posts

Posted - 2009-07-07 : 08:08:22

select convert(varchar(12),getdate(),103)

SYSTEM ANALYST

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources