SQLTeam.com | Weblogs | Forums

Convert hexadecimal date to date or string format

tsql
sql2008
sql2014
sql2008r2

#1

Hi,

I have date in hexadecimal format 0x3230313520303620323520313320353920333220303339 into 'normal human readable format'

Thanks

I tried select CONVERT(VARCHAR(8000),0x3230313520303620323520313320353920333220303339)

simply not giving me back proper format.

I need year/date/month/hour/minutes(if possible seconds)

Thanks.


#2

When you say not the right format, do you get the right date?
If so format as you want it. Note that slash between month/hour/minute is not a SQL format.


#3

or any other standard format that I've ever heard of


#4

what does that value represent in date ?

What is your "proper format" ?


#5
WITH Example(StrDate)
AS
(
    SELECT CAST(0x3230313520303620323520313320353920333220303339 AS char(19))
)
SELECT CAST
    (
        REPLACE(LEFT(StrDate, 10), ' ', '-')
        + ' '
        + REPLACE(SUBSTRING(StrDate, 12, 8), ' ', ':')
        AS datetime2(0)
    )
FROM Example;