covert date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

covert date

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-04 : 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

52326 Posts

Posted - 2012-07-04 : 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

77 Posts

Posted - 2012-07-11 : 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

52326 Posts

Posted - 2012-07-11 : 12:41:59

whats the datatype of createddate column and variables @startdate and @enddate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-11 : 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

277 Posts

Posted - 2012-07-11 : 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

52326 Posts

Posted - 2012-07-11 : 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

77 Posts

Posted - 2012-07-11 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-11 : 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

52326 Posts

Posted - 2012-07-11 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-11 : 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

52326 Posts

Posted - 2012-07-12 : 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

77 Posts

Posted - 2012-07-13 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-13 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources