Solved

# convert int to smalldatetime

Posted on 2007-07-31
Medium Priority
1,287 Views
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
Question by:sdc248
• 4
• 3

LVL 70

Accepted Solution

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

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

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

Author Comment

ID: 19601669
Oh, lahousden's solution works. Thanks a lot.
0

LVL 70

Expert Comment

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

Author Comment

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

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

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

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
Course of the Month12 days, 19 hours left to enroll