ansaurus

Question

Answer 1

A:

STEP 1: Let's get the date and save it into @Date variable.

DECLARE @Date DATETIME;
SELECT @Date = CONVERT(DATETIME, '1:00PM' , 100);

Now @Date = 1900-01-01 13:00:00.000

STEP 2: Let's convert it into custom format

SELECT CONVERT(VARCHAR, @Date, 101) + ' '
       + REPLACE(LTRIM(SUBSTRING(CONVERT(VARCHAR, @Date, 131), 12, 14)),
       ':000', ' ');

This returns '1/1/1990 1:00:00 PM'

Koistya Navin 2009-04-03 10:13:44

i need date format as 1/1/1900 1:00:00 PM.Now the result iam getting as 1900-01-01 13:00:00.000

2009-04-03 10:53:22

Can u give appropriate code?

2009-04-03 10:57:47

@ramyatk06, here it is.

Koistya Navin 2009-04-03 11:09:37

Answer 2

+1 A:

select convert(datetime , "1/1/1900 " + "1:00 PM" , 101)

Learning 2009-04-03 10:15:13

Got one error as "Conversion failed when converting datetime from character string."

2009-04-03 10:48:53

the space in '1/1/1900 ' is significant. I hope you did not overlook it!

Learning 2009-04-03 10:53:59

Answer 3

A:

See "CAST and CONVERT" in sql server books online

dd/mm/yy hh:mi:ss:mmmAM

you probably want 131 but return it as a varchar

so

SELECT CONVERT(VARCHAR(20),GETDATE(),131)

adolf garlic 2009-04-03 10:18:47

Conversion format from time to default datetime