Date Time Question - 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 2005 Forums

Transact-SQL (2005)

Date Time Question

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2010-07-12 : 16:37:34

I have a table A with Field Called CreationDate(Date Time Field).

I have 2 fields in Reports

a) CreatedDateFrom : Which will be like >= '06/02/2010'
b) CreatedDateTo: Which will be like <= '09/02/2010'

So Question is User can enter either 06/02/2010 or 6/2/2010.

So when I do :

Select * from table
Where Convert(Varchar(10),CreationDate,101) = '6/2/2010'. It doesn't return any rows.

So how to handle both scenerio.. Thanks a lot.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-12 : 16:53:36

The simple answer is to not convert it:

Select * from table
Where CreationDate >= '6/2/2010' and CreationDate < '6/3/2010'

You should be using a date/time parameter though, so it should be sent to SQL Server the same each time regardless what the user enters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-12 : 17:08:24

and it is better to pass dates in ccyymmdd format...for example

Where CreationDate >= 20100602' and CreationDate < '20100603'

which removes any locale based ambiguity

SCHEMA
Posting Yak Master

192 Posts

Posted - 2010-07-12 : 17:13:16

Thanks but my question is

User can enter any combination:

02/06/2010 or 2/6/2010 or 02/6/2010. We should be handling all possible scenerio..

And there is no required format they have to enter..

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-12 : 17:16:20

it will work either way they enter it, but if u require it like i showed, there will be no chance of problem.

in fact if the user is entering dates via an application, the app should have a date picker control, and convert it internally to the proper format

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-12 : 17:22:07

is this for an SSRS report? if so and you leave the date field parameter data type text, then user could enter 'Mickey Mouse' and then you will have problems. as the rest are suggesting convert it under the hood but better yet restrict the entry at source.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

SCHEMA
Posting Yak Master

192 Posts

Posted - 2010-07-12 : 17:24:32

yosiasz,

I didn't get it.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-12 : 17:48:17

yosiasz is telling you to make sure the parameter is of the correct data type -- datetime, or smalldatetime

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources