sql server programming:
HI

My problem is that I have a table with date column in it. I want to
retrieve the date value in a particular format which is of date type.

For this I used the CONVERT function but I am getting the following
error:

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

The query I used :

SELECT CONVERT(datetime, CONVERT(varchar(6), base_table."date1", 112),
112)

Thanks in advance

Sridhar Mamidi

Hi Sridhar

Is this the exact query you are using? If so, SQL Server can't figure out
what base_table."date1" means.

Also, you are losing all you've gained by converting back to datetime. It's
already stored in the database as datetime, and datetime values always are
displayed in a format determined by the system, not by you. You can only
control the format when you convert datetime to a character string and then
display character data. The system does not try to do any further conversion
on character strings.

If you need to get the date value from a table, something like this instead:

SELECT CONVERT(varchar(6), date1, 112)
FROM base_table

--
HTH
Kalen Delaney, SQL Server MVP


[quoted text, click to view]

You need to convert it to only varchar(8)
SELECT CONVERT(varchar(8), base_table.date1, 112)


[quoted text, click to view]

Hi Sridhar,
Just to add the icing on the cake (so to speak) the title of your post
states a format like "yyyy/dd" (i.e. with the slash). To do this try:

SELECT STUFF(CONVERT(char(6), base_table.date1, 112), 5, 0, '/')

-Tav.-

Tavis Pitt

I have another formating question.

Goal - keep column in datatime instead of convert to varchar
Current - '2006-06-21 16:54:33.000'
Wants - '2006-06-21 16:54:33'

Any help is appreciated!
-Lawrence

[quoted text, click to view]