Date Format

Will the following statement give me the date in the format of dd/mm/yyyy?
Set @.begindate = Convert(Datetime, @.begindate, 103)Also is it possible to do a search of a range on a field that is defined as
varchar. Example: data is imported into a table from a text file. The data
contains the date of when a project finished. The date is imported as
varchar. I need to know the projects that finished in a given w. The date
string is imported with the format dd/mm/yyyy.
Create Table Project
(ProjectID int Primary Key,
DateStart Varchar(10),
DateStop Varchar(10),
ProjectLeadID
)
Select *
From Project
Where DateStart >= 'date'
And DateStop <= 'another date'
"A.B." wrote:

> Will the following statement give me the date in the format of dd/mm/yyyy?
> Set @.begindate = Convert(Datetime, @.begindate, 103)|||A.B.,
SQL Server does not store datetime values in a specific format. See
"datetime and smalldatetime" in BOL for more info.
AMB
"A.B." wrote:

> Will the following statement give me the date in the format of dd/mm/yyyy?
> Set @.begindate = Convert(Datetime, @.begindate, 103)|||Try,
declare @.sd datetime
declare @.ed datetime
set @.sd = '20050101'
set @.ed = '20050908'
Select *
From Project
Where
right(DateStart, 4) + substring(DateStart, 4, 2) + left(DateStart, 2) >=
@.sd
And right(DateStop, 4) + substring(DateStop, 4, 2) + left(DateStop, 2) <
dateadd(day, 1, @.ed)
Do not expect that sql server perform an index s if there is a index by
DateStart and / or DateStop.
AMB
"A.B." wrote:
> Also is it possible to do a search of a range on a field that is defined a
s
> varchar. Example: data is imported into a table from a text file. The data
> contains the date of when a project finished. The date is imported as
> varchar. I need to know the projects that finished in a given w. The da
te
> string is imported with the format dd/mm/yyyy.
> Create Table Project
> (ProjectID int Primary Key,
> DateStart Varchar(10),
> DateStop Varchar(10),
> ProjectLeadID
> )
> Select *
> From Project
> Where DateStart >= 'date'
> And DateStop <= 'another date'
> "A.B." wrote:
>|||I believe what you're looking for is:
Set @.begindate = Convert(varchar, @.begindate, 103)
"A.B." wrote:

> Will the following statement give me the date in the format of dd/mm/yyyy?
> Set @.begindate = Convert(Datetime, @.begindate, 103)|||In order to search on a range of dates, you'll need to convert the varchar
columns to datetime. Something like this:
Select *
From Project
Where convert(datetime, DateStart) >= 'date'
And convert(datetime, DateStop) <= 'another date'
"A.B." wrote:
> Also is it possible to do a search of a range on a field that is defined a
s
> varchar. Example: data is imported into a table from a text file. The data
> contains the date of when a project finished. The date is imported as
> varchar. I need to know the projects that finished in a given w. The da
te
> string is imported with the format dd/mm/yyyy.
> Create Table Project
> (ProjectID int Primary Key,
> DateStart Varchar(10),
> DateStop Varchar(10),
> ProjectLeadID
> )
> Select *
> From Project
> Where DateStart >= 'date'
> And DateStop <= 'another date'
> "A.B." wrote:
>|||I am using the statement to set a variable later in my query I am going to
use the variable to pass that date to a SP that I wrote. But I need to the
format of the date to be dd/mm/yyyy.
Alter Procedure ButtonPushesRan
@.begindate VarChar(11),
@.enddate VarChar(11)
AS
Set @.begindate = Convert(Datetime, @.begindate, 103)
Set @.enddate = Convert(Datetime, @.enddate, 103)
SELECT DISTINCT TOP 100 PERCENT LotID, TDateStop
FROM dbo.LotInfoData
WHERE TDateStop <= @.begindate
or TDateStop >= @.endDate
ORDER BY LotID
"Alejandro Mesa" wrote:
> A.B.,
> SQL Server does not store datetime values in a specific format. See
> "datetime and smalldatetime" in BOL for more info.
>
> AMB
> "A.B." wrote:
>|||On Thu, 8 Sep 2005 07:06:24 -0700, A.B. wrote:

>Also is it possible to do a search of a range on a field that is defined as
>varchar. Example: data is imported into a table from a text file. The data
>contains the date of when a project finished. The date is imported as
>varchar. I need to know the projects that finished in a given w. The dat
e
>string is imported with the format dd/mm/yyyy.
>Create Table Project
>(ProjectID int Primary Key,
>DateStart Varchar(10),
>DateStop Varchar(10),
>ProjectLeadID
> )
(snip)
Hi A.B.,
Make sure you check the validity of the date when doing the import. Than
convert it to datetime format. That will make all queries on your dates
lots easier.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 8 Sep 2005 07:56:04 -0700, A.B. wrote:

>I am using the statement to set a variable later in my query I am going to
>use the variable to pass that date to a SP that I wrote. But I need to the
>format of the date to be dd/mm/yyyy.
>Alter Procedure ButtonPushesRan
>@.begindate VarChar(11),
>@.enddate VarChar(11)
>AS
>Set @.begindate = Convert(Datetime, @.begindate, 103)
>Set @.enddate = Convert(Datetime, @.enddate, 103)
>SELECT DISTINCT TOP 100 PERCENT LotID, TDateStop
>FROM dbo.LotInfoData
>WHERE TDateStop <= @.begindate
> or TDateStop >= @.endDate
>ORDER BY LotID
Hi A.B.,
If both the TDateStop column and your variable are defined as varchar,
you'll be doing string comparisons. That means that '01/09/2006' will be
considered to be less than '31/08/2005'. I doubt that is what you want
to achieve. As I said in my other post - convert the imported data to
datetime and store it as such.
Also, remove the TOP 100 PERCENT from the SELECT statement - a SELECT
statement will return all rows by default.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)