Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Datetime format Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2014 12:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,890, Visits: 2,329
Hi all,

Declare @date datetime= '18-06-2014 11:38:46.050'
select cast(@date as varchar)
Message: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Declare @date datetime= '2014-06-18 11:40:04.753'
select cast(@date as varchar)
Output: Jun 18 2014 11:40AM

How can I convert different datetime format to a common datetime format so that it can work for different time formats/



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1582692
Posted Wednesday, June 18, 2014 12:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:05 AM
Points: 13,724, Visits: 10,680
You are trying to store the value 18 in a month, which is not possible of course.
You can either use CONVERT to convert the string to a datetime. You only need to specify the correct format.

DECLARE @date DATETIME= CONVERT(DATETIME,'18-06-2014 11:38:46.050',105); -- apparently the Italian format
SELECT CAST(@date AS VARCHAR);

Or you can use SET DATEFORMAT to override the implicit date formatting.

SET DATEFORMAT dmy;

DECLARE @date DATETIME= '18-06-2014 11:38:46.050';
SELECT CAST(@date AS VARCHAR);





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1582699
Posted Wednesday, June 18, 2014 1:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 43,002, Visits: 36,158
Also

kapil_kk (6/18/2014)
select cast(@date as varchar)


Don't declare or cast varchars without a length definition. The default will bite you sooner or later.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1582718
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse