Date Time Manipulation (Should be interesting) !!!

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

SQL Server 2000 Forums

SQL Server Development (2000)

Date Time Manipulation (Should be interesting) !!!

Author

Topic

jubilanttiger
Starting Member

19 Posts

Posted - 2005-06-16 : 10:28:57

HI,

I have two int variables and one date variable

DECLARE @EXPYEAR INT,
@EXPMONTH INT,
@CREDITCARDDATE DATETIME

SET @EXPYEAR = 2005
SET @EXPMONTH = 6

I want to assign CREDITCARDDATE as 2005/6/1 as the date.

But since here @EXPYEAR and @EXPMONTH are integers and available seperately, I dont know how to convert them together into a date.

Do you have ideas of how I can do this ?

Thanks a lot for your time.

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-06-16 : 10:50:27

declare @expyear varchar(4)
declare @expmonth varchar(2)
declare @stringdate varchar(10)
declare @date smalldatetime

set @expyear = '2005'
set @expmonth = '06'
set @stringdate = @expyear + '-' + @expmonth + '-01'
print @stringdate
set @date = convert(smalldatetime, @stringdate, 111)
print @date

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-06-16 : 10:51:34

Note: I'm a beginner so if someone else has a better solution, don't be surprised.

jubilanttiger
Starting Member

19 Posts

Posted - 2005-06-16 : 11:02:48

But the problem is that I have @EXPYEAR , @EXPMONTH as integers and what to convert them into a date time within a query.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-16 : 11:08:56

This should do it:


declare @EXPYEAR int
declare @EXPMONTH int
declare @CREDITCARDDATE datetime

set @EXPYEAR = 2005
set @EXPMONTH = 6

select @CREDITCARDDATE =
	dateadd(dd,@EXPMONTH-1,dateadd(yy,@EXPYEAR-1900,0))

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-16 : 12:09:07

Michael's solution should be solid for you.

Also, this might be helpful for you:

http://weblogs.sqlteam.com/jeffs/archive/2003/12/15/657.aspx

Creating a UDF is really the way to go here, though:

http://weblogs.sqlteam.com/jeffs/archive/2003/12/09/646.aspx

There really should be a built-in date creation function in SQL Server instead of relying on converts and string conversions ....

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources