CODEWITHSUNDEEP
sql
user615611
user615611

Reputation: 184

Getting time from datetime in sql server

Can some body tell me what's wrong with the following code. I am trying to get the time part but it is not working.

declare @LssiTime nvarchar(30) 
declare @NewLssiTime varchar(5)

set @LssiTime = '2015-03-18 11:45:47.390'
SELECT  @NewLssiTime = CONVERT(varchar(5), @LssiTime,108) 
select @NewLssiTime

Upvotes: 0

Views: 78

Answers (2)

GarethD
GarethD

Reputation: 69819

The reason why this does not work is because @LssiTime is a varchar, therefore the style 108 does nothing. Another example would be:

SELECT CONVERT(VARCHAR(5), 'CONVERSTION TEST', 108);

-------
CONVE

Just because you read '2015-03-18 11:45:47.390' as a date, does not change the fact that you have told SQL Server it is a nvarchar(30).

The good news is that SQL Server is geared up for dealing with dates and times without nvarchar or varchar, there is a number of date and time formats and functions, which mean you don't need to rely on string conversion.

Which brings me to another problem with your string format. If you ran:

SET DATEFORMAT MDY;
DECLARE @Date DATETIME = '2015-03-18 11:45:47.390';
SELECT CAST(@Date AS TIME(0));

You get the result of 11:45:47. If you were to run this with different regional settings:

SET DATEFORMAT DMY;
DECLARE @Date DATETIME = '2015-03-18 11:45:47.390';
SELECT CAST(@Date AS TIME(0));

You get an error:

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

The only culture invariant format when dealing with DATETIME and SMALLDATETIME in SQL Server is yyyyMMdd, so you can run the following with any settings without an error:

DECLARE @Date DATETIME = '20150318 11:45:47.390';
SELECT CAST(@Date AS TIME(0));

If You insist on using strings, then at the very least us an appropriate string, a valid datetime will not contain unicode characters so you may as well use varchar, as with the above, casting to time will implicitly convert your string to a date so you can still use:

DECLARE @Date VARCHAR(30)  = '20150318 11:45:47.390'
SELECT  CAST(@Date AS TIME(0));

If you then want your time as a varchar(5), you must still convert to a time or datetime before converting to varchar:

DECLARE @Date VARCHAR(30)  = '20150318 11:45:47.390'
DECLARE @Time VARCHAR(5) = CAST(CAST(@Date AS TIME(0)) AS VARCHAR(5));
SELECT @Time;

Upvotes: 3

Ubaid Ashraf
Ubaid Ashraf

Reputation: 885

Try this : SELECT @NewLssiTime = cast(@LssiTime as time)

It will work for you scenerio.

Also after reading another answer, the reason why it didn't work is because sql server regards @LssiTime as varchar and not as datetime. In that case this will you will have to do like this:

Set  @NewLssiTime = CONVERT(char(5),Convert(datetime,@LssiTime) ,108)

Upvotes: 1

Related Questions