DateTime - 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

General SQL Server Forums

New to SQL Server Programming

DateTime

Author

Topic

bhanu2217
Starting Member

35 Posts

Posted - 2010-05-31 : 23:38:23

want to match only date in the database and not time

select * from table where date=Date;

Where both date & Date are in standard DateTime format.

www.JamboreeBliss.com

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-01 : 00:48:31

Try:

DATEADD(dd, DATEDIFF(dd,0, <your date field>), 0)

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-06-01 : 01:16:49

quote:
Originally posted by pk_bohra

Try:

DATEADD(dd, DATEDIFF(dd,0, <your date field>), 0)

can you a clarification plz

KaShYaP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-01 : 01:54:51

select * from table
where
date_col=dateadd(day,datediff(day,0,getdate()),0) and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)

Madhivanan

Failing to plan is Planning to fail

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-01 : 03:03:49

A small correction.. I am sure its a typo error..

select * from table
where
date_col>=dateadd(day,datediff(day,0,getdate()),0) and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)

I am here to learn from Masters and help new bees in learning.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-01 : 03:06:08

quote:
Originally posted by pk_bohra

A small correction.. I am sure its a typo error..

select * from table
where
date_col>=dateadd(day,datediff(day,0,getdate()),0) and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)

I am here to learn from Masters and help new bees in learning.

Yes It is
Thanks

Madhivanan

Failing to plan is Planning to fail

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-06-01 : 03:30:19

the modified code works

KaShYaP

bhanu2217
Starting Member

35 Posts

Posted - 2010-06-01 : 06:31:58

Thank You.

but slight change

The time that i am entering into query is not in standard SQL format

select * from Register WHERE
Date>=dateadd(day,datediff(day,0,'6/1/2010 12:00:00 AM'),0) and
Date<dateadd(day,datediff(day,0,'6/1/2010 12:00:00 AM')+1,0)

I get 0 result, I have 2 record for this date in the database 1st June 2010

www.JamboreeBliss.com

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-01 : 06:46:05

One of the reason may be the dateformat .

May be the DATEFORMAT is DMY

What is the output you are getting for below select statement
select convert(varchar,getdate(),101)

bhanu2217
Starting Member

35 Posts

Posted - 2010-06-01 : 06:51:47

quote:
Originally posted by pk_bohra

One of the reason may be the dateformat .

May be the DATEFORMAT is DMY

What is the output you are getting for below select statement
select convert(varchar,getdate(),101)

This is the output i am getting
06/01/2010

using SQL Server 2008 R2 64 bit.

www.JamboreeBliss.com

bhanu2217
Starting Member

35 Posts

Posted - 2010-06-01 : 06:55:25

To my surprise

even if i am doing
select * from Register
i still get 0 record.

Using Management studio express

www.JamboreeBliss.com

bhanu2217
Starting Member

35 Posts

Posted - 2010-06-01 : 06:57:40

I think i got the problem.

Please don't bother.

Thank you.

www.JamboreeBliss.com

Kristen
Test

22859 Posts

Posted - 2010-06-01 : 07:48:48

'6/1/2010 12:00:00 AM'

is ambiguous, don't use that style of "string dates".

Use
'yyyymmdd' - e.g. 20100601 - note there are no hyphens or other punctuation
'yyyymmdd hh:mm:ss.sss'
or
'yyyy-mm-ddThh:mm:ss.sss' - note that with this format hyphens ARE required.

guruchi_20
Starting Member

24 Posts

Posted - 2010-06-04 : 02:40:08

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS DateOnly, GETDATE() AS [Time], GETDATE() + .166664 AS [first], GETDATE() + .333328 AS [second]

try this one.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 03:19:42

See why only two formats are unambiguous
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources