Solved

Convert to datetime

Posted on 2012-03-25
5
243 Views
Last Modified: 2012-03-28
How do I convert this string to datetime?
'25.3.2012 19:41:07'

DECLARE @TimeDay datetime
SET @TimeDay='25.3.2012 19:41:07'
0
Comment
Question by:johnkainn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 5

Expert Comment

by:chinawal
ID: 37763431
DECLARE @TimeDay datetime
SET @TimeDay= replace('25.3.2012 19:41:07','.','/')

Assuming that your server is  dd/mm/yyyy format
0
 

Author Comment

by:johnkainn
ID: 37763545
When I run this I get following error:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

 When I select GETDATE() I get 2012-03-25 21:30:54.107

When I use following it works
DECLARE @TimeDay datetime
SET @TimeDay='2012-03-25 20:01:54.937'
0
 
LVL 5

Accepted Solution

by:
chinawal earned 250 total points
ID: 37763640
oK. try this. this should work.


DECLARE @TimeDay datetime
SET @TimeDay= convert(datetime, '25.3.2012 19:41:07', 104)
select @TimeDay
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 37765138
Hi, use the following query sample, this would help you;


DECLARE @v DATETIME
SELECT @v = CONVERT(DATETIME,'25.3.2012 19:41:07',105)
SELECT @v
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37766594
One of the only 100% safe date formats is YYYYMMDD, so it's safest -- although the code is longer -- to convert to 'YYYYMMDD HH:MM:SS.sss', which works under ALL SQL date and language settings.


DECLARE @TimeString varchar(30)
DECLARE @TimeDay datetime

SET @TimeString = '25.3.2012 19:41:07'

SET @TimeDay = PARSENAME(LEFT(@TimeString, CHARINDEX(' ', @TimeString) - 1), 1) +
RIGHT('0' + PARSENAME(LEFT(@TimeString, CHARINDEX(' ', @TimeString) - 1), 2), 2) +
RIGHT('0' + PARSENAME(LEFT(@TimeString, CHARINDEX(' ', @TimeString) - 1), 3), 2) +
SUBSTRING(@TimeString, CHARINDEX(' ', @TimeString), 30)

SELECT @TimeDay
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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