converting datetime problem - 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)

converting datetime problem

Author

Topic

poole
Starting Member

6 Posts

Posted - 2006-11-28 : 05:54:04

having a problem and if anyone can help it would be greatly appreciated!

A current field is stored as a varchar(500), users enter dates into this field by selecting from a calender.
The field values when selected in query analyser are shown as
2006/07/02 15:20:00
I need to convert the field to an english date but I am having problems....

if I try and run the following convert statement
convert(smalldatetime, Value, 103) Value2
i get an error
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

i tried another conversion of
convert(SMALLDATETIME,SUBSTRING(VALUE,9,2) + SUBSTRING (VALUE,5,4)+ SUBSTRING (VALUE,1,4)+SUBSTRING (VALUE,11,9),103) Value2
but i get the following results
2006-07-02 15:20:00
so in effect the date is still in the same format.

i still cant use this in my WHERE statement when trying to put in english date formats

any ideas????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 06:01:19

Whenever you convert a datetime back to DATETIME, it is presented in your locale, in QA and EM/SSMS.
Do the formatting of DATETIME at your client application.

Dates are internally stored as decimals, where the integer part is number of days passed since January 1, 1900 and the fraction part is number of 1/300 of a second since midnight.

How about you post a real query, some sample data and the expected output based on the provided sample data?

Peter Larsson
Helsingborg, Sweden

poole
Starting Member

6 Posts

Posted - 2006-11-28 : 06:13:25

this is my current query that i am trying to get working, it works fine at the moment as i am entering the dates yyyy/mm/dd in the WHERE clause but I want to be able to enter them as dd/mm/yyyy.

DROP TABLE #Test
(select MyDatabase.LGNCC_FORMDATAITEM.FormInstanceID, MyDatabase.LGNCC_FORMDATAITEM.Value
INTO #Test
from MyDatabase.LGNCC_FORMDATAITEM, MyDatabase.LGNCC_CaseFormInstance
where MyDatabase.LGNCC_CaseFormInstance.formInstanceid=MyDatabase.LGNCC_FORMDATAITEM.formInstanceid and itemkey ='dateRemoved')

SELECT
FormInstanceID,
CrewRemoved,
Subject,
CASE_Reference,
CREATED_DT,
CLOSED_DT,
ClassType,
Queue,
CaseStatus,
Minutes=DATEDIFF (n,CrewOpened,CrewRemoved)

FROM
(SELECT
CFI.FormInstanceID,
--The following subqueries are required to extract particular rows from LGNCC_FORMDATAITEM
--Also the date conversion is necessary as the date is stored as a string in this column
(select convert(SMALLDATETIME,SUBSTRING(VALUE,9,2) + SUBSTRING (VALUE,5,4)+ SUBSTRING (VALUE,1,4)+SUBSTRING (VALUE,11,9),103)
from MyDatabase.LGNCC_FORMDATAITEM where CFI.formInstanceid=MyDatabase.LGNCC_FORMDATAITEM.formInstanceid and itemkey ='dateRemoved') CrewRemoved,
(select value from MyDatabase.LGNCC_FORMDATAITEM where CFI.formInstanceid=MyDatabase.LGNCC_FORMDATAITEM.formInstanceid and itemkey ='Subject') Subject,
FCED.CASE_Reference,
FCED.CREATED_DT,
FCED.CLOSED_DT,
FCD.Type ClassType,
FCQD.name Queue,
FCED.Status CaseStatus
FROM
MYDATABASE.FLODS_CASE_ENQUIRY_F00 FCEF
INNER JOIN MyDatabase.FLODS_CASE_ENQUIRY_D00 FCED on FCEF.CASE_ENQUIRY_ID = FCED.FLODS_ID
INNER JOIN MyDatabase.FLODS_CASE_QUEUE_D00 FCQD on FCEF.QUEUE_ID = FCQD.FLODS_ID
INNER JOIN MyDatabase.FLODS_CLASSIFICATION_D00 FCD on FCEF.CLASSIFICATION_ID = FCD.FLODS_ID
INNER JOIN MyDatabase.lgncc_caseforminstance as CFI on FCED.CASE_Reference=CFI.CaseID
INNER JOIN #Test on CFI.formInstanceid=#Test.formInstanceid
WHERE 1=1
AND #Test.Value between '2006/07/01' and '2006/08/29'
AND FCED.Status = 'Closed'
AND FCD.Type = 'Crew Report - FlyTip'
) A
ORDER BY TypeOfWaste,CASE_Reference

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 06:15:30

Use ISO datetime format

AND CONVERT(DATETIME, #Test.Value) between '20060701' and '20060829'

Peter Larsson
Helsingborg, Sweden

poole
Starting Member

6 Posts

Posted - 2006-11-28 : 06:37:18

thanks that worked fine in my query but i am still having to put the dates in a yyyymmdd format when I would like to be able to enter them as ddmmyyyy.
Is there anyway I can convert the field to display in the english format in my temp table and then use that field to query in the WHERE clause?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-28 : 06:42:58

How about:

AND CONVERT(DATETIME, #Test.Value, 103) between CONVERT(DATETIME,'01/07/2006',103) and CONVERT(DATETIME,'29/08/2006', 103)

Harsh Athalye
India.
"Nothing is Impossible"

poole
Starting Member

6 Posts

Posted - 2006-11-28 : 06:45:29

just tried that and i get the error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 07:07:08

Don't forget to put

SET DATEFORMAT dmy

first.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 07:16:35

Where do you input the date as ddmmyyyy?

Peter Larsson
Helsingborg, Sweden

poole
Starting Member

6 Posts

Posted - 2006-11-28 : 08:29:55

i wanted to be able to put the date in as dd/mm/yyyy in my WHERE statement so that eventually I can use parameters for users to be able to put whatever dates they require in the english format.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 08:32:25

If you use

SET DATEFORMAT dmy

SELECT ...
FROM ...
WHERE ...
AND CONVERT(DATETIME, #Test.Value) between '1/7/2006' and '29/8/2006'

this should work.
Why is it SO important to use dates in that format?
Is this query built from a dynamic SQL statement?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-28 : 08:44:41

>>A current field is stored as a varchar(500), users enter dates into this field by selecting from a calender.

You should always use proper datatype DATETIME which is specifically used to stored dates. Otherwise you will end up with so many conversions

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-11-28 : 09:23:09

is this a stored procedure? Where does the date come from?

You should be using a datetime variable that is set to a proper date value at the client. Then you simply use this date variable throughout your SQL. SQL Server should not be concerned with date formats, only that a proper date parameter has been passed in. Your client applications need to handle all formatting and the work of converting user input into a valid datetime. This is not something that you should do in SQL Server, it isn't designed for that.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources