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

Query error Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 5:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 7:51 AM
Points: 9, Visits: 23
Hi i am getting the below error when i run this query.

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

Declare @EndDate Datetime
select @EndDate = convert(varchar(10), getdate(), 101) +CAST(DATEPART(hh,getdate()) AS VARCHAR) +':'+ CAST(DATEPART(mi,getdate()) as VARCHAR) +':'+ CAST(DATEPART(ss,getdate()) AS VARCHAR)+CAST(DATEPART(ss,getdate()) AS VARCHAR)
print @EndDate

please let me know the correct query

Post #1489145
Posted Wednesday, August 28, 2013 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 6:46 AM
Points: 5, Visits: 45
Try this..

Declare @EndDate varchar(25)
select @EndDate =CONVERT(varchar,(convert(varchar(10), getdate(), 101) + ' ' + CAST(DATEPART(hh,getdate()) AS VARCHAR) +':'+ CAST(DATEPART(mi,getdate()) as VARCHAR) +':'+ CAST(DATEPART(ss,getdate()) AS VARCHAR)+CAST(DATEPART(ss,getdate()) AS VARCHAR)),109 )
print @EndDate
Post #1489171
Posted Wednesday, August 28, 2013 7:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 12,993, Visits: 12,407
What exactly are you trying to do here? It looks like you are just trying to format the current date? Have you looked at CONVERT?

http://technet.microsoft.com/en-us/library/ms187928.aspx

Generally speaking it is better if you can leave the formatting to the front end instead of doing it in sql.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1489211
Posted Wednesday, August 28, 2013 8:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 6,748, Visits: 13,889
shafibinyunus (8/28/2013)
Try this..

Declare @EndDate varchar(25)
select @EndDate =CONVERT(varchar,(convert(varchar(10), getdate(), 101) + ' ' + CAST(DATEPART(hh,getdate()) AS VARCHAR) +':'+ CAST(DATEPART(mi,getdate()) as VARCHAR) +':'+ CAST(DATEPART(ss,getdate()) AS VARCHAR)+CAST(DATEPART(ss,getdate()) AS VARCHAR)),109 )
print @EndDate


This returns incorrect results, as does the original. Sean has the right idea. Try this:

SELECT
[Getdate] = GETDATE(),

[Old version] = CONVERT(varchar,(convert(varchar(10), getdate(), 101) + ' '
+ CAST(DATEPART(hh,getdate()) AS VARCHAR) + ':'
+ CAST(DATEPART(mi,getdate()) as VARCHAR) + ':'
+ CAST(DATEPART(ss,getdate()) AS VARCHAR)
+ CAST(DATEPART(ss,getdate()) AS VARCHAR)),109 ),

[New version] = CONVERT(CHAR(11),GETDATE(),101)
+ STUFF(CONVERT(VARCHAR(11),GETDATE(),114),9,1,'')



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1489266
Posted Monday, September 9, 2013 1:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:33 AM
Points: 7,107, Visits: 12,660
SELECT  FORMAT(GETDATE(), 'MM/dd/yyyy HH:mm:ss') 
AS [try the FORMAT function available in SQL 2012 and above];



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1492671
Posted Monday, September 9, 2013 1:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 6,748, Visits: 13,889
opc.three (9/9/2013)
SELECT  FORMAT(GETDATE(), 'MM/dd/yyyy HH:mm:ss') 
AS [try the FORMAT function available in SQL 2012 and above];



Thanks Orlando, I'll have a play with this at home when time permits.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1492675
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse