Datetime in sql

Question

Post reply

Datetime in sql

cpinky01

Ten Centuries

Points: 1144
More actions
September 13, 2010 at 10:51 am

#387773

I have one column in table table1 that has period month of integer format.
In this table I have data like this
PeriodMonth
201008-------------this means 2010 year and 08 month
201008
201007
201006
IN OTHER TABLE Tbale2 I have columns like this
StartMonth, EndMonth, StartDate, EndDate here startmonth and end month are of type integer and StartDate, EndDate are in datetime format.
Data is some thing like this
StartMonth, EndMonth, StartDate, EndDate
201007, 201009, 2010-07-01 00:00:00.000, 2010-09-30 23:59:59.000
201010, 201012, 2010-10-01 00:00:00.000, 2010-12-30 23:59:59.000
I need to compare if the periodMonth of table one lies inbetween the start date and end date of tbale2.
So for this I need to convert periodmonth to datetime format and then compare.
How can I do this.
I need to covert PeriodMonth of 201008 to 2010-08-01 00:00:00.000
How to write query for this.
can any please help me with this
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31168 More actions · Answer 1

Try:declare @Date varchar(10)

set @Date = '201008'

select cast(right(@Date,2) + '/01/' + left(@Date,4) as datetime)

Edit: I missed this part:

I have one column in table table1 that has period month of integer format.

I just tried it out; as far as I can tell, it should still work.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

LutzM SSC Guru Points: 107049 More actions · Answer 2

Why do you need to compare it to StartDate, EndDate instead of StartMonth, EndMonth?

If the latter, you could compare it without any data type conversion. (not that I like storing date values as integer in the first place... But if both tables have such a "semi-optimal" date format, why not using it?)

If you still need to do the conversion I'd rather go with the DATEFIRST independent format instead of the MDY format:

SELECT CAST(CAST(201008 AS CHAR(6)) +'01' AS DATETIME)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

cpinky01 Ten Centuries Points: 1144 More actions · Answer 3

Hello lutzm,

i am not clear. is it that i can write query some thing like this.

select t2.* from table 1 t1 innerjoin table 2 on (t1.periodmonth beteen t2.statmonth ad t2.endmonth

)

can you please explain clearly.

thank for replying.

thank you

cpinky01 Ten Centuries Points: 1144 More actions · Answer 4

hi lutzm,

period month,startmonth,endmonth are of type integers.still can i compare directly.does it give correct period results?

thank you

Ray K SSC-Dedicated Points: 31168 More actions · Answer 5

What Lutz and I are showing you are ways to convert your year/month (201008) into a datetime format that you can compare against other dates. That way, you can "compare apples to apples" (so to speak).

In terms of comparing the result to other dates, I'd suggest looking at the DATEDIFF function in BOL.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/