Solved

Convert to datetime

Posted on 2012-03-25
5
239 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
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:ScottPletcher
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now