CODEWITHSUNDEEP
sql-server
prateek
prateek

Reputation: 33

CAST () OR CONVERT () not changing date-time format to string in SQL Server 2008

I have a column which is of datetime type.
I want to separate date and time for which i used left() & right() enclosed by cast() or convert() like below :

Select BATCH, 
LEFT( CAST(S_DATE AS VARCHAR(20)),12) AS ST_DATE, 
RIGHT( CAST(S_DATE AS VARCHAR(20)),8) AS ST_TIME,
LEFT( CAST(E_DATE AS VARCHAR(20)),12) AS E_DATE, 
RIGHT( CAST(E_DATE AS VARCHAR(20)),8) AS E_TIME
INTO CRYST2
From Z_BATCH;

this is my actual format for datetime column :-

2015-10-01 14:00:00.000

But the problem is that it is separating the date and time accurately but it isn't returning the output in string form, as the output itself turns to date & time format respectively in their respective columns. Following is the output I am getting:-

1   Oct  1 2015       2:00PM    Oct  1 2015       2:30PM

As you can clearly see the columns I separated after date-time to string conversion is still giving me output in some date-time format only. please help regarding it.

Upvotes: 2

Views: 2881

Answers (4)

Guffa
Guffa

Reputation: 700342

this is my actual format for datetime column :

2015-10-01 14:00:00.000

No, it's not. A datetime value doesn't have a format at all, it only represents a point in time. It only gets a format when you convert it to a string.

You are not specifying any format at all when you convert the datetime values, so it will be using the default format (0). You can use the format that you saw the datetime values displayed as (121) to get the desired result:

Select BATCH, 
LEFT( CAST(S_DATE AS VARCHAR(19),121),10) AS ST_DATE, 
RIGHT( CAST(S_DATE AS VARCHAR(19),121),8) AS ST_TIME,
LEFT( CAST(E_DATE AS VARCHAR(19),121),10) AS E_DATE, 
RIGHT( CAST(E_DATE AS VARCHAR(19),121),8) AS E_TIME
INTO CRYST2
From Z_BATCH;

Upvotes: 1

Arishummootil Appukuttan
Arishummootil Appukuttan

Reputation: 1

You can try this, if you want to get results in string format:

select convert(varchar(11),cast(S_DATE  as date),106)
select convert(varchar(11),cast(S_DATE  as time),100)

If you try to get answers in date and time format, try this:

CAST(S_DATE AS Time) AS ST_TIME
CAST(S_DATE AS Date) AS ST_Date

Upvotes: 0

Eric
Eric

Reputation: 5743

Use CONVERT with format 120 to get the desired result, it always return the format yyyy-MM-dd hh:mm:ss

CONVERT(varchar(20), S_DATE, 120)

Upvotes: 0

ziad mansour
ziad mansour

Reputation: 349

Cast The date and the Time in the exact format like:

CAST(S_DATE AS Time) AS ST_TIME
CAST(S_DATE AS Date) AS ST_Date

will return:

14:00:00
2015-10-01

Upvotes: 0

Related Questions