Solved

Convert to datetime

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

820 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