How to get todays date in format YY/MM/DD and to compare it to another date passed into th

I need to do the following and am hoping someone can help me out.

I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to the
stored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the date
passed into the stored proc needs to be compared to todays date. Todays date must be determined in
the SQL.

So basically here is my pseudo code for what I am trying to accomplish. Basically I just am after
the comparison of the two values:

If @.BeginDate < TodaysDate

The difficult part is how to obtain the value for "TodaysDate"

Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in.

Can someone please code this out for me in Microsoft SQL. I would be forever grateful.

I figured out what I needed to know, but will have further questions and will need help. Thanks to all.

how to get Time time difference in sql?

Could any one please tell me how to find time difference between two dates?

i have two fields in database as datetime data type.i need to get time difference between this two fields.how to do that?

i use this one

SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

but it giving me results as

1.00000000

1.250000

3.00000

i jus want it to be

1

1.25

3

How to do this?

Thanks for any help.

To find the time difference in days, hours, minutes. Try it, you can replace the dates with you column datetime.

selectDATEDIFF(day,2007-11-30,2007-11-20)AS NumberOfDays,DATEDIFF(hour,2007-11-30,2007-11-20)AS NumberOfHours,DATEDIFF(minute,2007-11-30,2007-11-20)AS NumberOfMinutesFROM test_table

|||

hi, thanks for the reply i dint useDATEDIFF(hour,2007-11-30,2007-11-20) this option jus because

i have one data as 10:45 and another as 12:00 it gives me time diff as 2 in hrs but actual diff is only 1:15 min thats y im convertin to min first and dividing it by 60.

but my outputs are like 1.02345,2.4567

i jus need to be like 1, 2.45

|||

Tweety@.net:

SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

simple...

just write this query as...

SELECT outdate, ROUND(cast((datediff(mi, outtime, intime) / 60.0) as FLOAT),2) AS TimeUtilized FROM breaktime

and it should work.

hope this helps./.

|||

Try casting the result to decimal data type. The syntax is cast ( <number> as decimal ( 5 , 2 ) ).

sql

How to get Time in SmallDateTime?

Hi,
I just want to get the value from a smalldatetime into time only in a SQL Statement
10/10/2005 10:00:00 AM -- > 10:00:00 AM
What would be the best approached?
Thanks,
VinceThis will truncate all datetime values to Jan 1, 1900, leaving only the time portion. To format the resulting value, look up the CAST and CONVERT functions.
declare @.MyTime smalldatetime
set @.MyTime = getdate()
select dateadd(d, -datediff(d, 0, @.MyTime), @.MyTime)

How to get thru security of Sql server http endpoint?

I have write the coding to create http endpoint in the sql server 2005, but if i type my pc ownself ip address (ex, 192.168.0.110) to connect to the sql server http endpoint, it prompt me for Username and Password. I didn't set any username and password before, what should i type ? Or what should i do to pass thru this authentication?

Thanks in advance.

Cheers,

Winson

Hi Winson,

For security reasons, the SQL Server 2005 http endpoints for Native XML Web Services require the client application to send user credentials. By default, the user account who created the HTTP endpoint will have access to connect to the endpoint. Users of the sysadmin role will also have access to the endpoint. All other SQL login accounts, must be granted explicit connect permissions. Please refer to Books Online's (BOL) GRANT Endpoint permission topic available at http://msdn2.microsoft.com/en-us/library/ms187811.aspx for additional information. For information regarding how to specify user credentials in the client application in .Net Frameworks, please refer to the following BOL topic http://msdn2.microsoft.com/en-us/library/ms175929.aspx.

Jimmy

|||

Hello Jimmy,

Thanks for the help, actually the situation is like this. I using nusoap (php) to connect to the Sql server http endpoint. but i fail to pass thru the security of sql sever http endpoint over the Internet. What do you think? Do you have any idea regarding this this of connection? nusoap (php) <> sql server http endpoint?

Thanks in advanced.

Cheers,

Winson

how to get this resultset...

Hi,
I have 3 tables as follow :
Kanji :
kanji_id
...
References :
ref_id
...
KanjiRefs
kref_idkanji
kref_idref
kref_value
...
So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...
I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?
2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL
I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3
however, this obviously does not include kanjis having no reference
#3...
any help would be greatly appreciated, thanks!
ibiza
Try the following (untested):
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
Razvan
|||wow! It all works :P
many thanks!
Razvan Socol wrote:
> Try the following (untested):
> SELECT kanji_id FROM Kanji
> WHERE kanji_id NOT IN (
> SELECT kref_idkanji FROM KanjiRefs
> WHERE kref_idref=3
> )
> SELECT kanji_id, (
> SELECT kref_value FROM KanjiRefs
> WHERE kref_idref=3
> AND kref_idkanji=kanji_id
> ) as kref_value
> FROM Kanji
> Razvan

how to get this resultset...

Hi,

I have 3 tables as follow :

Kanji :
kanji_id
...

References :
ref_id
...

KanjiRefs
kref_idkanji
kref_idref
kref_value
...

So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...

I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?

2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL

I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3

however, this obviously does not include kanjis having no reference
#3...

any help would be greatly appreciated, thanks! :)
ibizaTry the following (untested):

SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)

SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji

Razvan|||wow! It all works :P

many thanks!

Razvan Socol wrote:

Quote:

Originally Posted by

Try the following (untested):
>
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
>
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
>
Razvan

how to get this resultset...

Hi,
I have 3 tables as follow :
Kanji :
kanji_id
...
References :
ref_id
...
KanjiRefs
kref_idkanji
kref_idref
kref_value
...
So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...
I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?
2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL
I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3
however, this obviously does not include kanjis having no reference
#3...
any help would be greatly appreciated, thanks! :)
ibizaTry the following (untested):
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
Razvan|||wow! It all works :P
many thanks!
Razvan Socol wrote:
> Try the following (untested):
> SELECT kanji_id FROM Kanji
> WHERE kanji_id NOT IN (
> SELECT kref_idkanji FROM KanjiRefs
> WHERE kref_idref=3
> )
> SELECT kanji_id, (
> SELECT kref_value FROM KanjiRefs
> WHERE kref_idref=3
> AND kref_idkanji=kanji_id
> ) as kref_value
> FROM Kanji
> Razvansql