Date Format - 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

Date Format

Author

Topic

BCJ
Starting Member

42 Posts

Posted - 2008-06-24 : 12:10:49

Hi,

How could i select the data between a particular range.The below statement is not returning any data , but data exists. And, when i tried with '>' and '<' i'm not getting the exact data. could any body show the correct syntax for this...........Thanks

SELECT * from tbl_1 where JDate between '10/29/04' and '03/25/08'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 12:13:45

looks like JDate contains date & time

specify your date in ISO format YYYYMMDD

try


SELECT * from tbl_1 where JDate between '20041029' and '20080325'

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

BCJ
Starting Member

42 Posts

Posted - 2008-06-24 : 12:25:51

tried with the suggestion and results are in the same way. jdate dont have the time stored in it. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 12:31:48

quote:
Originally posted by BCJ

tried with the suggestion and results are in the same way. jdate dont have the time stored in it. Thanks.

same way as ? no result ? or ?

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

BCJ
Starting Member

42 Posts

Posted - 2008-06-24 : 12:36:33

yes, no result.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 12:50:32

what is the data type of JDate column?

Madhivanan

Failing to plan is Planning to fail

BCJ
Starting Member

42 Posts

Posted - 2008-06-24 : 12:54:43

nvarchar(10) :), so what could be the solution....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 12:57:14

quote:
Originally posted by BCJ

nvarchar(10) :), so what could be the solution....

Why did you use nvarchar datatype to store dates?
You should always use proper DATETIME datatype to store dates

Try

SELECT * from tbl_1 where cast(JDate as DATETIME) between '20041029' and '20080325'

Madhivanan

Failing to plan is Planning to fail

BCJ
Starting Member

42 Posts

Posted - 2008-06-24 : 13:04:24

Thanks a lot , that works.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 13:06:26

quote:
Originally posted by BCJ

Thanks a lot , that works.

It is better you create a new column with proper DATETIME datatype. Update dates from nvarchar column and use this DATETIME column for further manipulation

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources