?
Solved

Need some help with DATEPART

Posted on 2011-05-03
8
Medium Priority
?
463 Views
Last Modified: 2012-05-11
Hello,
I would like to get all the data from a time period.
from 1Day,1Month,1Year     to     9Day,9Month,9Year
f.e.    01,03,2011                 to     31,03,2011

      sql.Format("SELECT * FROM ViewLieferscheine WHERE \
                           DATEPART(year,month,day,Lieferscheindatum)= %d,%d,%d BETWEEN DATEPART(year,month,day,Lieferscheindatum)= %d,%d,%d \
                   ORDER BY CAST(LieferscheinNr AS BIGINT)DESC ",1Day,1Month,1Year,9Day,9Month,9Year);

this code will not work. Where is my mistake?

The row of the table is a datetime like this 03.05.2011 (SQL Server 2000)
500 points with a solution.
Best regards,
Thomas
0
Comment
Question by:tsp2002
8 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35512811
select * from table
 where CONVERT(nvarchar(30),YuorColumn,103)
                         between '01-03-2011' and '31-03-2011'


read :http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35512887
declare @startdate datettime, @enddate datetime

set @startdate =  '01-03-2011'
set @enddate =  '31-03-2011'

select * from table1
where Lieferscheindatum >= @startdate
and Lieferscheindatum <= @enddate
0
 
LVL 3

Expert Comment

by:JaseemK
ID: 35513200
-- Try this...                  
SELECT *
FROM ViewLieferscheine
WHERE Lieferscheindatum BETWEEN '20110501' AND '20110531'
go
-- This will return all entries where Lieferscheindatum is in May 2011.

-- You can also declare DATETIME variables and use these within your query:

DECLARE @ThisDay DATETIME   -- I'm using @ThisDay instead of your Lieferscheindatum field.
SELECT @ThisDay = GETDATE()

DECLARE @1Day DATETIME, @1Month DATETIME, @1Year DATETIME, @9Day DATETIME, @9Month DATETIME, @9Year DATETIME
SELECT  @1Day = DATEPART(DD, @ThisDay),
      @1Month = DATEPART(MM, @ThisDay),
      @1Year = DATEPART(YYYY, @ThisDay)

SELECT  @9Day = DATEADD (DD, 1, @ThisDay),
      @9Month = DATEADD (MM, 1, @ThisDay),
      @9Year = DATEADD (YYYY, 1, @ThisDay)
-- So assuming today's 3rd May 2011, the above will set the '@9' vars to 4th June 2012.

select CAST(@1Day as BIGINT)
select CAST(@1Month as BIGINT)
select CAST(@1Year as BIGINT)

select @9Day
select @9Month
select @9Year

etc..
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 3

Expert Comment

by:JaseemK
ID: 35513860
-- You need to include the time in the second date so that the whole day is included:
SELECT *
FROM ViewLieferscheine
WHERE Lieferscheindatum BETWEEN '20110501' AND '20110531 23:59:59.999'
go
--HTH
             
SELECT *
FROM ViewLieferscheine
WHERE Lieferscheindatum BETWEEN '20110501' AND '20110531'
go
0
 

Author Comment

by:tsp2002
ID: 35687589
Hello,
thanks for the answers

JaseemK
your last answer will work.
SELECT *
FROM ViewLieferscheine
WHERE Lieferscheindatum BETWEEN '20110501' AND '20110531 23:59:59.999'
go



how can I use a place-holder like %d?
f.e. I think I have to use a CAST? the table row Lieferscheindatum is a DATETIME

CString start_date="20110501";
CString stop_date="20110531 23:59.999";

 sql.Format("SELECT * FROM ViewLieferscheine WHERE \
                                    WHERE Lieferscheindatum BETWEEN %d AND %d\
                                    ORDER BY CAST(LieferscheinNr AS BIGINT)DESC ",start_date,stop_date);

please help, thank you.
Best regards,
Thomas



10.BMP
0
 
LVL 3

Accepted Solution

by:
JaseemK earned 2000 total points
ID: 35687698
Declare your 'place holders' as normal T-SQL DATETIME variables and use these within the SELECT code:

DECLARE @start_date DATETIME, @stop_date DATETIME

-- The assignments below will be accepted as DATETIME types
SELECT @start_date = '20110501'
SELECT @stop_date = '20110531 23:59.999'

SELECT *
FROM ViewLieferscheine
WHERE Lieferscheindatum BETWEEN @start_date AND @stop_date
go

-- Your vars must be declared as DATETIME type.
HTH.
0
 

Author Comment

by:tsp2002
ID: 35688220
Hi,
I can not DECLARE the start and stop because the user adds this dates in a Editbox that I read
I tried this....but it will not work. So I guess I have to convert the CSTring to Datetime....can you help?

CString a="20110301";
CString b="20110531 23:59.999";
COleDateTime start;
start.ParseDateTime(a);

COleDateTime stop;
stop.ParseDateTime(b);


            sql.Format("SELECT * FROM ViewLieferscheine WHERE \
                            Lieferscheindatum  BETWEEN %d AND %d \
                    ORDER BY CAST(LieferscheinNr AS BIGINT)DESC",start,stop);
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35689639
you can use:-

select convert(datetime,'20110531 23:59.999',1)

Open in new window

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question