Query error

Question

Post reply

Query error

smharabbani40

SSC Enthusiast

Points: 143
More actions
August 28, 2013 at 5:50 am

#281732

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

shafibinyunus Valued Member Points: 53 More actions · Answer 1

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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