?
Solved

Convert to datetime

Posted on 2012-03-25
5
Medium Priority
?
245 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 1000 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 70

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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