Query: default getdate() field

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

SQL Server Development (2000)

Query: default getdate() field

Author

Topic

db_sysadmin
Starting Member

27 Posts

Posted - 2006-03-16 : 10:49:44

Hey guys,

I changed some field default to getdate(), now how am I able to query for a certain date regardless time...

Ex.

SELECT * FROM TRANSACTIONS WHERE Date_Field = '02/02/2006'

and as well if I want to query that very same field on a certain time range

SELECT * FROM TRANSACTIONS WHERE Date_Field > '02:02'

When getdate returns stuff like this:

SELECT Getdate()

2006-03-16 12:48:55.397

What are the possible solutions and alternatives to this matter?

Thanks in advance...

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-16 : 11:31:08

for the first query, you can do:


SELECT * FROM TRANSACTIONS WHERE Date_Field >= '2006-02-02' and Date_Field < '2006-03-02'

the second query:


SELECT * FROM TRANSACTIONS WHERE datepart(hh,Date_Field) > '02' and datepart(hh,Date_Field) < '03'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 19:59:23

quote:
When getdate returns stuff like this:

SELECT Getdate()

2006-03-16 12:48:55.397

What are the possible solutions and alternatives to this matter?

Use this as your default

select dateadd(day, datediff(day, 0, getdate()), 0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-17 : 00:13:20

Read more about querying dates
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

db_sysadmin
Starting Member

27 Posts

Posted - 2006-03-17 : 09:45:44

Hey Guys,

I tried changing the date field default, but I couldn't get it to work as I expected. using this:

DATEADD(day,DATEDIFF(d,0,getdate()),0)

CAST(DATEDIFF(d,0,GETDATE()) AS DATETIME)

with a select statement they both work perfectly, but as a default they keep storing date as this: 2006-03-17 12:31:00.000 instead of

2006-03-17 00:00:00.000

What's wrong?

Thansk a bunch!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-17 : 09:52:37

How did you set the default value ? using the default or via query ?


create table #test
(
	col1	int,
	col2	datetime	default dateadd(day, datediff(day, 0, getdate()), 0)
)

insert into #test(col1) select 10

select * from #test

col1        col2                                                   
----------- ------------------------------------------------------ 
10          2006-03-17 00:00:00.000

(1 row(s) affected)

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-17 : 09:54:31

Dont worry about the storage. Apply your logic in where clause only

Madhivanan

Failing to plan is Planning to fail

db_sysadmin
Starting Member

27 Posts

Posted - 2006-03-17 : 10:15:57

I set it thruogh enterprise manager, design table -> field -> default

I know what you mean madhivanan, is just that some clients can't handle the date that way, and reports aren't grouping well as they should...

Thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources