Is it possible to get hour from datetime if the column in nvarchar

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

Is it possible by using DatePart() method to get hours from datetime where date is in nvarchar.
I am trying to get that but getting an error like:
Msg 241, Level 16, State 1, Line 14
Conversion failed when converting date and/or time from character string.

the sample query is:

SQL

Declare @Hours Table (Hour int);
 
Declare @i as int;
Set @i = 0
While @i < 24
Begin
   Insert into @Hours
   Values(@i);
 
   Set @i = @i + 1;
End;
 
 
Select
   [Hour of Day],
   Count([Total Calls]) as total
From
(
   SELECT
        DATEPART(HOUR, convert(Datetime,ShipDate,13)) AS [Hour of Day]
      , Id AS [Total Calls]
   FROM ShipmentDetail
   Union ALL
   SELECT Hour, NULL
   From @Hours
) as Data
 
GROUP BY [Hour of Day]
ORDER BY [Hour of Day]

please modify the query.

Thanks&Regards,
Raghu.

Posted 7-Aug-12 23:31pm

Madhugundi

Updated 8-Aug-12 0:00am

AmitGajjar

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**AmitGajjar** · Answer 1 · 2012-08-08T00:03:00

Solution 1

Hi,

If you have varchar datatype to store your data, you need to parse your date and covert it into DateTime and then you can get your desired part from the datetime.

like,

C#

DateTime myDateTime = DateTime.Parse("yourstringdate");
int myDay = myDateTime.Day;

Here i have used Parse method but instead you can use TryParse to make your code with less error probability.

Hope this information helps you,

Thanks
-Amit Gajjar.

Posted 8-Aug-12 0:03am

AmitGajjar

Comments

Madhugundi 8-Aug-12 6:06am

How to parse varchar to datetime in sql

AmitGajjar 8-Aug-12 6:11am

http://stackoverflow.com/questions/1135746/sql-server-convert-string-to-datetime

see solution in above link

AmitGajjar 8-Aug-12 6:12am

you can use CONVERT function of sql

OriginalGriff · Answer 2 · 2012-08-08T00:10:00

No, not really.

The problem is that not all cultures represent dates and times the same way, so without knowing exactly what format your user was using when they stored the date time value in the database, you can't design a simple, reliable way to extract date or time information from a string. It is possible, certainly, but it may not be practical, as the number of options and special cases may be prohibitive.

A much, much better solution is to not store dates or times as strings at all, but to convert them to DateTime values as soon as possible, and store those instead. That way, you stand a good chance of converting from a user-specific local format to a Datetime and never have to worry about it again.

Aarti Meswania · Answer 3 · 2012-08-08T00:16:00

Solution 3

yes but you should have data in nvarchar field in below format
yyyy-dd-MM hh:mm:ss
when converting to datetime

and
yyyy-MM-dd hh:mm:ss
when comparing with date-time field

Happy coding!
:)

Posted 8-Aug-12 0:16am

Aarti Meswania

Updated 8-Aug-12 3:26am

v2

Comments

Madhugundi 8-Aug-12 7:20am

yes i have data like:
2012-07-25 08:22:48.643(PST)
How is it possible just modify my code.

Aarti Meswania 8-Aug-12 9:24am

select convert(Datetime,(
substring('2012-07-25 08:22:48.643',1,5)
+ substring('2012-07-25 08:22:48.643',9,3)+
+ substring('2012-07-25 08:22:48.643',5,3)
+ substring('2012-07-25 08:22:48.643',11,9)),105)

Santhosh Kumar Jayaraman · Answer 4 · 2012-08-08T05:31:00

Solution 4

When it is varchar, why do you want to convert to datetime and get hours. You can directly substring.

SQL

create table temptable
(dat nvarchar(100))

insert into temptable values('2012-07-25 08:22:48.643(PST)')

select substring(dat,12,12) from temptable

Posted 8-Aug-12 5:31am

Santhosh Kumar Jayaraman