help needed in get date from datetime

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

help needed in get date from datetime

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-18 : 05:25:01

Hi,

this is my query

select dateName(weekDay,LOADDATE) from emp where LOADDATE
between dateadd(day, -1,'02/27/2010') and dateadd(day, -6,'02/27/2010')

Here LoadDate is datetime. but this query donesn't show data becase '02/27/2010' is string. but i need to get data. so how could we modify this query to get data. please help me

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 05:29:31

try this,

select dateName(weekDay,LOADDATE) from emp where LOADDATE
between dateadd(day, -1,CONVERT(datetime, '02/27/2010', 103)) and dateadd(day, -6,CONVERT(datetime, '02/27/2010', 103))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-18 : 05:36:11

you got the between date reversed. Should be earlier date to later date


WHERE LOADDATE between dateadd(day, -6, '20100227') and dateadd(day, -1, '20100227')

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-18 : 05:47:13

select dateName(weekDay,LOADDATE) from emp where LOADDATE
between dateadd(day, -1,CONVERT(datetime, '02/27/2010', 103)) and dateadd(day, -6,CONVERT(datetime, '02/27/2010', 103))

gives error as

Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Hi khtan,

i need 20100227 date data also. so how could it be done? please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-18 : 05:54:15

1. does LOADDATE contain time also ?

quote:
i need 20100227 date data also. so how could it be done?

2. what is the date range of LOADDATE that you wanted ? Your initial query shows it is between 6 days ago and yesterday from the reference date 2010-02-27

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-18 : 06:15:11

Hi khtan,

thanks for your reply.

i ahve done it with your help. thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources