How do I SELECT a date? - 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)

How do I SELECT a date?

Author

Topic

amgrace
Starting Member

30 Posts

Posted - 2004-06-02 : 06:38:14

Hi
I need to select all the records between a certain period (e.g from 1/1/04 - 31/1/04)

The original format of the date column on my SQL Server database is:
2004-04-21 00:00:00

My query so far is:
select
CustomerNo,
FirstName,
Surname,
CONVERT(char(10),CustomerStartDate,103) as StartDate

where
---this is where I need the help. I want to choose all records for January---

Can you please help.Thanks

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-02 : 06:53:43

[code]
where myDate between '1/1/04' and '31/1/04'
[/code]

Note the setting of your dateformat, is it DMY or MDY? The above implicit converstion would work when dateformat is set to DMY.

Good luck.

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-02 : 06:54:49

[code]
where CustomerStartDate between '01-Jan-2004' and '31-Jan-2004'[/code]

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-02 : 06:57:32

Rick, you come second boy

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-02 : 07:07:16

Yeah, but mine will work for all date formats...

amgrace
Starting Member

30 Posts

Posted - 2004-06-02 : 07:15:27

Thanks. Both codes definitely retured data - Not sure yet if its correct though.

So another quick question - Amethystium mentioned noting if the setting of my dateformat is DMY or MDY as thehe above implicit converstion would work when dateformat is set to DMY.
How do I check this? When I do a simple select * from the table, the date is displayed in this format: 2004-04-21 00:00:00
Looks like YMD to me.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-02 : 07:20:50

It's not really a display issue. Sorry to confuse you with this, but I think Rick's suggestion is better in theis case.

Consider this :


set dateformat DMY

select cast('23/08/1978' as datetime)

Now change te dateformat to MDY and try :


set dateformat MDY

select cast('23/08/1978' as datetime)

Catch my drift?!

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

srinivasanr
Starting Member

15 Posts

Posted - 2004-06-02 : 07:22:12

Hi,
Its always recommended to use CONVERT when checking for date ranges..

WHERE myDate BETWEEN CONVERT(DATETIME,'01/01/04',103) AND CONVERT(DATETIME,'31/01/04', 103)

This will save you in convert to YMD etc.. date formats

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-02 : 08:11:03

quote:
Originally posted by Amethystium

It's not really a display issue. Sorry to confuse you with this, but I think Rick's suggestion is better in theis case.

Consider this :

set dateformat DMY

select cast('23/08/1978' as datetime)

Now change te dateformat to MDY and try :

set dateformat MDY

select cast('23/08/1978' as datetime)

Catch my drift?!

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

Score...

amgrace
Starting Member

30 Posts

Posted - 2004-06-03 : 06:23:35

Thank you all for your different tips.

I used the code below and it works fine.

WHERE MyDate BETWEEN CONVERT(char (10),'01/01/04',103) AND CONVERT(char (10),'31/01/04', 103)

once again, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources