?
Solved

convert int to smalldatetime

Posted on 2007-07-31
9
Medium Priority
?
1,287 Views
Last Modified: 2012-06-22
Hi:

How do I convert an int to a smalldatetime?
The int has a format of yyyyMMddmmss. For example, 200707302345 means 7/30/2007 23:45.

Thanks.
0
Comment
Question by:sdc248
  • 4
  • 3
8 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 19601537
DECLARE @value BIGINT
SET @value = 200707302345
SELECT CAST(STUFF(STUFF(CAST(@value AS VARCHAR(12)), 9, 0, ' '), 12, 0, ':') AS SMALLDATETIME)

[Btw, that value won't fit into an INT ... maybe it's a BIGINT?]
0
 
LVL 10

Assisted Solution

by:lahousden
lahousden earned 500 total points
ID: 19601579
int is too small, it will have to be a bigint.  E.g.:

declare @intdatetime bigint
declare @sdtdatetime smalldatetime
set @intdatetime = 200707302345

set @sdtdatetime = convert (smalldatetime,
substring (cast (@intdatetime as nvarchar), 1, 4) + '-' +
substring (cast (@intdatetime as nvarchar), 5, 2) + '-' +
substring (cast (@intdatetime as nvarchar), 7, 2) + ' ' +
substring (cast (@intdatetime as nvarchar), 9, 2) + ':' +
substring (cast (@intdatetime as nvarchar), 11, 2)
, 120)

select @sdtdatetime
0
 

Author Comment

by:sdc248
ID: 19601629
The value is in decimal(12,0). Sorry my mistake. Is this the reason why I am getting NULL using your solution?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:sdc248
ID: 19601669
Oh, lahousden's solution works. Thanks a lot.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19601683
I think only if the input value is NULL (that is, was not set).
0
 

Author Comment

by:sdc248
ID: 19601998
ScottPletcher:

I did some more tests and found that your solution was right, it was me making a mistake when testing it.  Now the points have been given to lahousden.  Do you know any way I can give you the same amount of points as well? Sorry.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19602268
Thanks, I appreciate that.

Depends on how many pts the initial q was for?  I think it was 150, but am not sure now.  If it was, you can open a q with a title like "Points for ScottPletcher for Q_22732029".  Remember, though, that according to EE rules, you cannot award more than 500 pts for any one q.
0
 

Author Comment

by:sdc248
ID: 19609485
I have doubled the assigned points (125, now 250) and splited it so ScottPletcher can get the points he deserves.

Thanks a lot guys.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question