Set datime and convert it

Question

Post reply

Set datime and convert it

hoseam

SSCertifiable

Points: 5533
More actions
July 21, 2014 at 5:35 am

#309265

Hi
I have this query:
declare @ToDate datetime
declare @FromDate datetime
set @FromDate = ?
set @ToDate = ?
select @FromDate
select @ToDate
To what values can I set my FromDate to, I tried set @FromDate = '21/07/2014' but I get and error since @FromDate is not a vachar, then I tried 21/07/2014 I also get '1900-01-01 00:00:00.000' date instead. please help. I want to assign values to my variables

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23000 More actions · Answer 1

set dateformat ymd

declare @ToDate datetime

declare @FromDate datetime

set @FromDate = '2014-07-21'

set @ToDate = '2014-07-22'

select @FromDate

select @ToDate

Does that do what you need?

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

CONVERT uses style codes to control output and input formatting:

declare @ToDate datetime, @FromDate datetime

SELECT -- UK format

@FromDate = CONVERT(DATETIME,'21/06/2014',103),

@ToDate = CONVERT(DATETIME,'21/07/2014',103)

select @FromDate, @ToDate

SELECT -- US format

@FromDate = CONVERT(DATETIME,'06/21/2014',101),

@ToDate = CONVERT(DATETIME,'07/21/2014',101)

select @FromDate, @ToDate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

hoseam SSCertifiable Points: 5533 More actions · Answer 3

@BWFC

The code is this:

declare @ToDate datetime
declare @FromDate datetime
set @FromDate = '01/02/20014'
set @ToDate = '28/02/20014'
SELECT EVENTDATE,'' AS BRANCHCD, BUSINESS_AREA, CREATE_DATE, WORK_TYPE, QUEUE, STATUS, CREATE_AGE, CATEGORYCD, DELAYREASON1, DELAYREASON2
FROM dbo.vwCS_RPT_EB_DRAFTERS_INFO_DESPATCHDOC
WHERE (CONVERT(datetime, EVENTDATE, 103) >= @FromDate) AND (CONVERT(datetime, EVENTDATE, 103) <= @ToDate)

And I get this error, Syntax error converting datetime from character string.

So I posted that piece of a code to try to solve this one

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

What datatype is EVENTDATE?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

hoseam SSCertifiable Points: 5533 More actions · Answer 5

hoseam

SSCertifiable

Points: 5533

July 21, 2014 at 7:14 am

#1730998

It's datetime.

Neil Burton SSC-Insane Points: 23000 More actions · Answer 6

hoseam (7/21/2014)
@BWFC
The code is this:
declare @ToDate datetime
declare @FromDate datetime
set @FromDate = '01/02/20014'
set @ToDate = '28/02/20014'
SELECT EVENTDATE,'' AS BRANCHCD, BUSINESS_AREA, CREATE_DATE, WORK_TYPE, QUEUE, STATUS, CREATE_AGE, CATEGORYCD, DELAYREASON1, DELAYREASON2
FROM dbo.vwCS_RPT_EB_DRAFTERS_INFO_DESPATCHDOC
WHERE (CONVERT(datetime, EVENTDATE, 103) >= @FromDate) AND (CONVERT(datetime, EVENTDATE, 103) <= @ToDate)
And I get this error, Syntax error converting datetime from character string.
So I posted that piece of a code to try to solve this one

Have you noticed that you've got your year as 20014? If you've copied and pasted directly, that might be your problem.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

hoseam SSCertifiable Points: 5533 More actions · Answer 7

I just corrected it and I get this error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

hoseam (7/21/2014)
I just corrected it and I get this error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Use CONVERT with a style, as I suggested.

Also, remove the conversion you have on EVENTDATE, it makes no difference to the result set but is likely to hinder performance.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

Use ISO based date formats or if you must do DMY set your connection to use DMY formatting

http://en.wikipedia.org/wiki/ISO_8601

SET DATEFORMAT DMY

Otherwise the general default is MDY resulting in 28/02/2014 being the 28th Month, 2nd Day, 2014th Year, and your getting a conversion error as there isn't a 28th month

hoseam SSCertifiable Points: 5533 More actions · Answer 10

declare @ToDate datetime
declare @FromDate datetime
set @FromDate = '1 Feb 2014'
set @ToDate = '28 Feb 0014'
SELECT EVENTDATE,'' AS BRANCHCD, BUSINESS_AREA, CREATE_DATE, WORK_TYPE, QUEUE, STATUS, CREATE_AGE, CATEGORYCD, DELAYREASON1, DELAYREASON2
FROM dbo.vwCS_RPT_EB_DRAFTERS_INFO_DESPATCHDOC
WHERE EVENTDATE >= convert(datetime, @FromDate,103) AND EVENTDATE <= convert(datetime, @ToDate,103)

The error is the same, "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

hoseam (7/21/2014)
declare @ToDate datetime
declare @FromDate datetime
set @FromDate = '1 Feb 2014'
set @ToDate = '28 Feb 0014'
SELECT EVENTDATE,'' AS BRANCHCD, BUSINESS_AREA, CREATE_DATE, WORK_TYPE, QUEUE, STATUS, CREATE_AGE, CATEGORYCD, DELAYREASON1, DELAYREASON2
FROM dbo.vwCS_RPT_EB_DRAFTERS_INFO_DESPATCHDOC
WHERE EVENTDATE >= convert(datetime, @FromDate,103) AND EVENTDATE <= convert(datetime, @ToDate,103)
The error is the same, "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

set @ToDate = '28 Feb 0014'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ant-Green SSC Guru Points: 113507 More actions · Answer 12

Your ToDate is 0014, that's outside the scope of datetime

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

DROP TABLE #Events

CREATE TABLE #Events (ID INT IDENTITY(1,1) NOT NULL, EVENTDATE DATETIME)

INSERT INTO #Events VALUES

('2014-07-20 14:42:33.360'),

('2014-07-19 14:42:33.360'),

('2014-07-18 14:42:33.360'),

('2014-07-17 14:42:33.360'),

('2014-07-16 14:42:33.360'),

('2014-07-15 14:42:33.360'),

('2014-07-14 14:42:33.360'),

('2014-07-13 14:42:33.360'),

('2014-07-12 14:42:33.360')

-- filter the table for dates between 14th and 19th June inclusive

DECLARE @FromDate DATETIME, @ToDate DATETIME

SELECT -- UK format

@FromDate = CONVERT(DATETIME,'14/07/2014',103),

@ToDate = CONVERT(DATETIME,'19/07/2014',103)

SELECT *

FROM #Events

WHERE EVENTDATE >= @FromDate AND EVENTDATE <= @ToDate

-- note that '2014-07-14 14:42:33.360' is included in the output because

-- '2014-07-14 14:42:33.360' > '2014-07-14 00:00:00.000'

-- but '2014-07-19 14:42:33.360' is excluded, because it's > 2014-07-19 00:00:00.000

-- so change the WHERE clause:

SELECT *

FROM #Events

WHERE EVENTDATE >= @FromDate AND EVENTDATE < DATEADD(DAY,1,@ToDate)

-- This filters correctly, and you don't have to convert EVENTDATE to DATE -

-- which *could* prevent index usage.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

hoseam SSCertifiable Points: 5533 More actions · Answer 14

hoseam

SSCertifiable

Points: 5533

July 21, 2014 at 7:57 am

#1731021

Thank you