Prev: How to update the Year in a date field?
Next: CTE parameters
From: gv on 9 Mar 2010 16:56
Hi all,

I used this to append to a some backups.

DECLARE @DATE VARCHAR(20) = CONVERT(VARCHAR(10),CURRENT_TIMESTAMP,112) + '-'
+ RIGHT('00' + CAST(DATEPART(HOUR,CURRENT_TIMESTAMP) AS VARCHAR(10)),2)
+ RIGHT('00' + CAST(DATEPART(MINUTE,CURRENT_TIMESTAMP)AS VARCHAR(10)),2)
+ RIGHT('00' + CAST(DATEPART(SECOND,CURRENT_TIMESTAMP)AS
VARCHAR(10)),2)

How can I convert this back to datetime?

After pasing out of the filename and removing the dash this is what is left:

20100303075951

thanks
gv


From: Bob Barrows on 9 Mar 2010 17:18
gv wrote:
> Hi all,
>
> I used this to append to a some backups.
>
> DECLARE @DATE VARCHAR(20) =
> CONVERT(VARCHAR(10),CURRENT_TIMESTAMP,112) + '-' + RIGHT('00' +
> CAST(DATEPART(HOUR,CURRENT_TIMESTAMP) AS VARCHAR(10)),2) +
> RIGHT('00' + CAST(DATEPART(MINUTE,CURRENT_TIMESTAMP)AS
> VARCHAR(10)),2) + RIGHT('00' +
> CAST(DATEPART(SECOND,CURRENT_TIMESTAMP)AS
> VARCHAR(10)),2)
>
> How can I convert this back to datetime?
>
> After pasing out of the filename and removing the dash this is what
> is left:
>
> 20100303075951
>
> thanks
> gv

Pretty much the same way you built it:use string functions to turn it
into a string that sql server will recognize as a valid datetime and
implicitly convert to a datetime. 'yyyymmdd hh:mm:ss' should work
Something like:

declare @strdate char(14), @date datetime
set @strdate = '20100303075951'
set @date = left(@strdate,8) + ' ' + substr(@strdate,9,2) + ':' +
substr(@strdate,11,2) + ':' + substr(@strdate,13,2)
select @date


--
HTH,
Bob Barrows


From: Plamen Ratchev on 9 Mar 2010 17:41
Here is another method:

DECLARE @dt VARCHAR(14);

SET @dt = '20100303075951';

SELECT
CONVERT(DATETIME,
STUFF(STUFF(STUFF(STUFF(STUFF(@dt, 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':'),
120);

--
Plamen Ratchev
http://www.SQLStudio.com
From: Bob Barrows on 10 Mar 2010 08:52
Plamen Ratchev wrote:
> Here is another method:
>
> DECLARE @dt VARCHAR(14);
>
> SET @dt = '20100303075951';
>
> SELECT
> CONVERT(DATETIME,
> STUFF(STUFF(STUFF(STUFF(STUFF(@dt, 5, 0, '-'), 8, 0, '-'), 11, 0, '
> '), 14, 0, ':'), 17, 0, ':'), 120);

I've been given to understand that the hyphenated format is not truly
"safe" to use, i.e., depending on the server's settings, there's a
chance it could be misinterpreted. See some of the "conversations"
between Tony Rogerson and CELKO. That is why I recommended the yyyymmdd
format.

--
HTH,
Bob Barrows


From: Plamen Ratchev on 10 Mar 2010 10:02
Note that I used CONVERT(DATETIME, <yyyy-mm-dd hh:mi:ss>, 120). If you look in BOL style 120 interprets date format
exactly in this format: yyyy-mm-dd hh:mi:ss. Therefore this is perfectly safe.

The past discussions around here regarding unsafe date formats are when you use them without explicitly converting using
a predetermined style.

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: How to update the Year in a date field?
Next: CTE parameters