2

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.

Ullas
  • 11,450
  • 4
  • 33
  • 50
prateek
  • 35
  • 2
  • 9
  • Don't cast to datetime to varchar. Instead, use dateadd and datediff to get midnight of the date, or ever datepart to get the different parts of the date. – Zohar Peled Oct 05 '15 at 05:30
  • @ZoharPeled, you mean to say that first separate date part from date-time format? means separate year, month and date part separately and then concatenate them? – prateek Oct 05 '15 at 05:41
  • OKI GOT IT !! CONVERT(date, S_DATE), CONVERT(TIME, S_DATE) IT gave the desired output.thanx all!! – prateek Oct 05 '15 at 05:58

4 Answers4

1

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;
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • [and for a bit more reading (and self promoting :-))](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) – Zohar Peled Oct 05 '15 at 09:24
0

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
ziad mansour
  • 349
  • 6
  • 25
0

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)
Eric
  • 5,675
  • 16
  • 24
0

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
Stanislav
  • 27,441
  • 9
  • 87
  • 82
Arishummootil Appukuttan
  • 1