Date Format Problem!!

Question

5.00/5 (1 vote)

See more:

C#

SqlCommand cmd = new SqlCommand("SELECT * FROM [t_attendence] where [t_scantime] =='" 2011-12-11 9:00:00 "'", conn);

Problem when comparing the date. i want to find out the data with the same day.
However now my t_scantime data is day with the hh:mm:ss, so i cant use the equal command. So is there any method to internally separate the t_scantime into day and time separately? Please anyone help.

Thanks

Posted 2-Jan-12 17:50pm

tomy_hkcg

Add a Solution

4 solutions

Solution 1

Hi tomy_hkocg,
Use following code to get only date from db.

SQL

SELECT CONVERT(varchar, MyDate, 101) FROM DatesTable

Compute Scalar(DEFINE:([Expr1004]=CONVERT(varchar(30),[TEST].[dbo].[DatesTable].[MyDate],101)))
Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, MyDate)) FROM DatesTable

Compute Scalar(DEFINE:([Expr1004]=dateadd(day, 0),CONVERT_IMPLICIT(datetime,datediff(day,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[TEST].[dbo].[DatesTable].[MyDate],0)),0))))

and Use this link
http://www.sql-server-helper.com/tips/date-formats.aspx[^]
to convert date formats at user interface.

Posted 2-Jan-12 18:00pm

Prasad_Kulkarni

Comments

tomy_hkcg 3-Jan-12 0:02am

o, it's seem not a easy one. gonna read this after my lunch break. thanks

thanks you

Solution 4

For your query, use parameters, see: SqlParameter[^].

When you define the date as a datetime parameter, you don't have to worry about the correct format anymore. Not to mention that it will help you against SQL injections etc.

Also if you just want to compare the date portion, you can use CAST or CONVERT. Something like:

SQL

...where CAST([t_scantime] AS date) = @t_scandate...

Posted 2-Jan-12 18:52pm

Wendelius

Comments

tomy_hkcg 3-Jan-12 1:25am

thanks you!!

Wendelius 3-Jan-12 8:41am

You're welcome :)

Solution 2

u can try this inur code if suppose u r getting date as string

string dtt="2011-12-11 9:00:00 ";
            DateTime dt =Convert.ToDateTime(dtt);
            dtt = dt.ToShortDateString();

Posted 2-Jan-12 18:15pm

anushripatil

Comments

Mehdi Gholam 3-Jan-12 1:01am

Please do not use chat/text speak when answering.

Sergey Alexandrovich Kryukov 3-Jan-12 1:41am

Right! When asking/commenting, too. This is considered rude; many feel very irritated.
--SA

tomy_hkcg 3-Jan-12 1:26am

thanks you

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)

Amir Mahfoozi · Accepted Answer · 2012-01-02T18:50:00

Solution 3

I think DATEDIFF [^]will solve your problem. So you have to do something like this :

C#

SqlCommand cmd = new SqlCommand("SELECT * FROM [t_attendence] where DATEDIFF(DAY, [t_scantime] , cast( '" + strDate + "' as datetime ) ) = 0 ", conn);

Hope it helps.

Posted 2-Jan-12 18:50pm

Amir Mahfoozi

Comments

tomy_hkcg 3-Jan-12 1:25am

done! love you!

Amir Mahfoozi 3-Jan-12 1:28am

Thank you :)

tomy_hkcg 3-Jan-12 2:01am

one more question, how about if i want to compare their time?! using >= or <=

Amir Mahfoozi 3-Jan-12 2:27am

To compare their time parts you can use DATEDIFF too, read and run this example to learn how it works :
select cast('2012-01-01 2:5:8' as time) , cast('2012-01-05 2:5:3' as time) ,
datediff(SECOND , cast('2012-01-01 2:5:8' as time) , cast('2012-01-05 2:5:3' as time))
Pay attention that the date part is not important in this example. If the result is negative you can conclude that the second time is less than the first one.
Good Luck

tomy_hkcg 3-Jan-12 2:29am

thanks get it. you are very helpful.

Amir Mahfoozi 3-Jan-12 2:34am

Good Luck.

k.Prathap 3-Jan-12 5:47am

good one

Amir Mahfoozi 3-Jan-12 5:50am

Thanks.