| Author |
 Topic  |
|
|
musclebreast
Yak Posting Veteran
56 Posts |
 Posted - 07/04/2012 : 16:53:23
|
Hello in my table time I've got a date with the following format:
2012-07-02 22:39:32.000
I need to check wether the date is between a start and end date (time is not important). Start and end date are have the following format:
10/23/2016
I tried it as followed:
select * from TABLE Where convert(datetime, createdate, 101) between convert(datetime, '10/23/2016', 101) AND convert(datetime, '10/23/2010', 101)
It doesn't work...has anybody a hint for me?
Kind regards,
Lara |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/04/2012 : 16:56:25
|
select * from TABLE where createdate >= convert(datetime, @startdate, 101) and createdate < dateadd(dd,1,convert(datetime, @enddate, 101))
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
musclebreast
Yak Posting Veteran
56 Posts |
Posted - 07/11/2012 : 12:14:16
|
Hello in my table time I've got a date with the following format:
20120711
I need to check wether the date is between a start and end date (time is not important). Start and end date are have the following format:
10/23/2016
My problem is similar to the first one..I tried to convert this Date but alwys I get the follwoing error:
select * from TABLE where convert(datetime,createdate,101) >= convert(datetime, @startdate, 101) and convert(datetime,createdate,101) < dateadd(dd,1,convert(datetime, @enddate, 101))
Arithmetic overflow error converting expression to data type datetime.
Kind regards,
Lara |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/11/2012 : 12:41:59
|
whats the datatype of createddate column and variables @startdate and @enddate?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 07/11/2012 12:42:35 |
|
|
|
musclebreast
Yak Posting Veteran
56 Posts |
Posted - 07/11/2012 : 14:13:59
|
OK, createDate is from type datetime,
@startdate and Enddate are from type string and I get it from a form, that's the reason why I try to convert it. If use a startdate as string in this format 20121222 then it works. I can convert it with javascript but i would like to do it on the database.
Can you give me a hint?
Kind regards,
Lara
|
|
|
|
xhostx
Constraint Violating Yak Guru
USA
261 Posts |
Posted - 07/11/2012 : 14:32:36
|
use convert function...eg:
according to your example, your date is in string format, so:
convert(datetime, '20121222', 112) as mydate
--------------------------
Get rich or die trying
-------------------------- |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/11/2012 : 15:51:42
|
quote:
Originally posted by musclebreast
OK, createDate is from type datetime,
@startdate and Enddate are from type string and I get it from a form, that's the reason why I try to convert it. If use a startdate as string in this format 20121222 then it works. I can convert it with javascript but i would like to do it on the database.
Can you give me a hint?
Kind regards,
Lara
if createddate is datetime isnt this enough?
where createdate >= convert(datetime, @startdate, 101) and createdate < dateadd(dd,1,convert(datetime, @enddate, 101))
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
musclebreast
Yak Posting Veteran
56 Posts |
Posted - 07/11/2012 : 17:24:54
|
Hi,
I played with the convert codes and it works with the code 103.
One more question, because I don't get it.
My startdaten is 11/07/2012 and my Enddate is 11/07/2012 as well...but no rows are shown...only when I increase the Enddate to 12/07/2012...why does the query not show the documents from one day?
Kind regards,
Lara |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 07/11/2012 : 17:44:17
|
when the time portion is excluded then 00:00:00.000 is implicit. So:
< 11/07/2012
means all dates up to 10/07/2012 11:59:59:59.
>= 11/07/2012 AND < 12/07/2012
means all dates between 11/07/2012 00:00:00.000 and 11/07/2012 11:59:59:59
Be One with the Optimizer
TG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/11/2012 : 17:48:59
|
quote:
Originally posted by musclebreast
Hi,
I played with the convert codes and it works with the code 103.
One more question, because I don't get it.
My startdaten is 11/07/2012 and my Enddate is 11/07/2012 as well...but no rows are shown...only when I increase the Enddate to 12/07/2012...why does the query not show the documents from one day?
Kind regards,
Lara
code 103? as per you initial explanation date format was mm/dd/yyyy then 101 should be code. 103 stands for dd/mm/yyyy
are you sure it works as intended?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 07/11/2012 : 18:07:17
|
I think we'll have to wait for July 13 to find out 
Be One with the Optimizer
TG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/12/2012 : 09:31:47
|
yep... I'm expecting a new post on July 13th telling that its not working anymore
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
musclebreast
Yak Posting Veteran
56 Posts |
Posted - 07/13/2012 : 19:07:08
|
Hi guys,
you are really funny:)it's cool:) No the code 103 was right, because the format of the date is 19/02/1972. From my point of view it's working fine now.
One thing I don't know and hope you can give me the answer. In my examples before I compared only the day. Now I want it more precise and include the minutes as well.
What I've got: createdate from type datetime (2012-07-13 22:27:12.620)in the format YYYY-MM-DD HH:MI:SS.MMM(24h)
No i've got a startdate Fri Jul 13 22:45:09 2012 ( I can convert it with Javascript to a different format. do I need to format ist?) and I want all documents which are created after this date.
here my SQL:
where createdate >= convert(datetime, @startdate, ???)
does that SQL check automatically the time (minutes) as well?
Kind regards
Lara
|
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 07/13/2012 : 19:58:14
|
Hi Lara,
Yes, sql will treat the datetime value like a single value including minutes and milliseconds. If you leave them off then as I said earlier 12:00 AM is implied. If you simply strip off the "Fri" then sql will convert the startdate as is. Don't even need the 3rd argument (format code). That will get you the time portion as well:
select convert(datetime, 'Jul 13 22:45:09 2012')
OUTPUT:
2012-07-13 22:45:09.000
Be One with the Optimizer
TG |
Edited by - TG on 07/13/2012 20:17:09 |
|
|
| |
Topic |
|
covert date
