SQL Text to Date Conversion

Neal · April 17th, 2007, 05:58 AM

Hi,

I'm using SQL Server 2000

[u]First</u>
In SQL Query Analyser I'm trying to convert a TEXT field into DATE FORMAT. The data within the table field 'Movement Date' was imported into SQL via DTS and auto set the field to varchar(255). As the DTS will be a regular transfer occurence, can I change this in a query rather than alter the field datatype each time?

The current format is (for example):
'05-Feb-1999 00:00:00'

I need to convert to DD/MM/YYYY ('05/02/1999') - I'm not that concerned whether the trailing TIME is included or not.

I found that CDate (SQL Function) works in Access. However, when the following is code run in Query Analyser it complains that ''CDATE' is not a recognized function name.'

Code:

SELECT [Movement Header Number], CDATE([Movement Date]), [Load Quantity],
[Packaged Item Number], [Fixing Header Number] FROM dbo.progress_movhdr

[u]Secondly</u>
I then need to test for date ranges (on 'Movement Date') in my query. Do I need to apply the 'BETWEEN' statement to test for a condition? Or a 'SELECT CASE'?

i.e.
If [Movement Date] >= 01/02/1999 AND
[Movement Date] <= 31/01/2000

The Dates are to be put in Year ranges and start around 1994.

Any ideas please?

Thanks in advance.

Neal

A Northern Soul

happygv · April 17th, 2007, 06:40 AM

You should use CONVERT() in place of CDate()

Code:

Select Convert(Varchar(10), [Movement Date], 103) FROM dbo.progress_movhdr

If you are referring apply criteria to the above said query... then it should be something like this.

Code:

SELECT [Movement Header Number], 
Convert(Varchar(10), Convert(DateTime, [Movement Date], 121), 103),
[Load Quantity],[Packaged Item Number], [Fixing Header Number]
FROM dbo.progress_movhdr
WHERE Convert(DateTime, [Movement Date], 121)
BETWEEN '1999-02-01 00:00' AND '2000-01-31 23:59'

Hope that helps.
Cheers

_________________________
- Vijay G
Strive for Perfection

Neal · April 17th, 2007, 07:55 AM

Thanks Vijay,

Just one thing..

Why '103' in the statement? What does that do exactly?

Neal

A Northern Soul

Neal · April 17th, 2007, 07:57 AM

I just tried the conversion, and got the following error:
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

Neal

A Northern Soul

happygv · April 17th, 2007, 09:58 AM

Quote:

quote:Originally posted by Neal
Thanks Vijay,
Just one thing..
Why '103' in the statement? What does that do exactly?

Neal
A Northern Soul

103 is the code used to get the date in dd/mm/yyyy format. It is the 3rd parameter for Convert function when you convert some value into DATETIME.

cheers.

_________________________
- Vijay G
Strive for Perfection

happygv · April 17th, 2007, 09:59 AM

Quote:

quote:Originally posted by Neal
I just tried the conversion, and got the following error:
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

Neal

A Northern Soul

That is because the Data you have in the column is not uniform. For example Try this on your query analyzer.

Code:

Select convert(datetime, '05-Feb-1999 00:00:00')

It works fine. But some of the rows might have the data in different format that the convert function is unable to convert that into a Date value.

Can you post the format in which the data is stored into that column before conversion?

_________________________
- Vijay G
Strive for Perfection

happygv · April 17th, 2007, 10:08 AM

Also please re-look my first post on this topic. I made some changes in BLUE Font to the SELECT statement that I posted earlier. It was my bad. I did some mistake in that. Please use that...

_________________________
- Vijay G
Strive for Perfection

Neal · April 17th, 2007, 10:44 AM

Hi Vijay,

Still getting an error...

Here is some of the data examples:

14-Sep-1998 00:00:00
22-Sep-1998 00:00:00
17-Sep-1998 00:00:00
02-Oct-1998 00:00:00

Thanks for your help.

Neal

A Northern Soul

happygv · April 17th, 2007, 11:16 AM

I doubt some value doesn't conform to converting into datetime. Can you check through all the rows?

_________________________
- Vijay G
Strive for Perfection

rstelma · April 17th, 2007, 01:44 PM

I had to find bad data in converting a datetime column from an Access database.

Selecting unique date parts was a big help.

SELECT DISTINCT DatePart(yy, [datecolumn]) AS UniqueYear FROM [Table]:

dd and mm also