convert from varchar to datetime

Question

Post reply

convert from varchar to datetime

abhavsar

SSC Rookie

Points: 31
More actions
June 10, 2009 at 11:11 am

#205674

SELECT date_id
FROM Summary_D
Brings data in following format.
May 17 2009 12:00AM
May 18 2009 12:00AM
May 22 2009 12:00AM
May 26 2009 12:00AM
May 27 2009 12:00AM
Jun 1 2009 12:00AM
May 20 2009 12:00AM
May 23 2009 12:00AM
Jun 4 2009 12:00AM
Jun 5 2009 12:00AM
How do I convert to
6/4/2009
6/50/2009
etc.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

Try this.

SELECT CONVERT(VARCHAR(10),CAST(date_id AS DATETIME),101)

FROM Summary_D

Also, what data type is date_id?

abhavsar SSC Rookie Points: 31 More actions · Answer 2

abhavsar

SSC Rookie

Points: 31

June 10, 2009 at 12:41 pm

#1007625

Thanks - it works.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

abhavsar (6/10/2009)
SELECT date_id
FROM Summary_D
Brings data in following format.
May 17 2009 12:00AM
May 18 2009 12:00AM
May 22 2009 12:00AM
May 26 2009 12:00AM
May 27 2009 12:00AM
Jun 1 2009 12:00AM
May 20 2009 12:00AM
May 23 2009 12:00AM
Jun 4 2009 12:00AM
Jun 5 2009 12:00AM
How do I convert to
6/4/2009
6/50/2009
etc.

Where will you be storing the dates once they're converted?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dk7525 SSC Enthusiast Points: 131 More actions · Answer 4

Hi,

If you want to use DD/MM/YYYY style pattern then you need add the parameter style number 103.

Here is the example,

Declare @temp table

(

i datetime

)

Insert into @temp values ('2006-03-30 12:46:11.700')

select convert(varchar(10),i,103) from @temp

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

I'd still like to know what the OP is going to do with the dates once they are converted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

abhavsar SSC Rookie Points: 31 More actions · Answer 6

FYI - The query results are used by an external application.