Ripping a date apart

Question

Ripping a date apart

fryere

Ten Centuries

Points: 1054
More actions
September 13, 2004 at 2:53 pm

#59978

I simply what to be able to Extract the Month, Day, and Year of a date. Make some changes if needed then stick it back to together as a datetime varible. Actually it is sticking it back together that is giving me the problems. Can someone point me to the correct function or syntax to do this.
Thanks
fryere

fryere

Viewing 12 posts - 1 through 11 (of 11 total)

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

CK Bhatia Say Hey Kid Points: 679 More actions · Answer 1

You can use the DATEPART function to extract a component of a datetime variable.

To stick it back together, you would need to CONVERT them into a string datatype and concatenate them. However, if the resulting value is an invalid date you would recieve an error. You can check for the validity of the date using the ISDATE function.

I feel the need - the need for speed

CK Bhatia

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 2

try this

DECLARE @Month varchar(50),

@Day varchar(50),

@Year varchar(50),

@Date datetime

Set @Month = '9'

Set @Day = '13'

Set @Year = '2004'

Set @Date = @Month + '/' + @Day + '/' + @Year

print @date

Everett Wilson
ewilson10@yahoo.com

~nano SSC Eights! Points: 804 More actions · Answer 3

retreiving dateparts:

SELECT DATEPART (month, getdate()) -- you can use day or year accordingly where needed

The above post will give you a datestring. To get this into s datetime variable use:

SELECT CONVERT(datetime, <datestring>, 103)

<datestring can be a variable or something like '01/01/2004' . 103 is the format that will be given back in the select (See CAST & CONVERT in BOL), but you do have to make sure, that the date to convert is a valid date

But my question is, if you want to change just a part of the date, for example day should be 15 instead of 8 why not use the DATEADD function:

SELECT DATEADD(year, x, getdate())

year can subsituted with day, month, week .... (See BOL). x should be an int (positive or negativ) .Decimals won't throw an error, but the value will be cut down to the last whole number. Using DATEADD will make sur that your date is valid and also save you having to convert.

greetings from Germany

~nano

fryere Ten Centuries Points: 1054 More actions · Answer 4

Thanks you all for the Help.

Grasshopper:

I tried using the DateADD Function but I couldn't get it to subtract days. Maybe there is a better way to do what I am trying to do. I am wanting to limit records by Fiscal Year.

Thanks.

fryere

JacekO SSCertifiable Points: 6610 More actions · Answer 5

In my opinion using the DATEADD function is the cleanest way to achive what you need. You do not have to create a messy logic to deal with changing months and years when adding or subtracting days. (don't even mention leap years...)

Try this to see how to subtract days:

SELECT DATEADD(day, -10, getdate())

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 6

Quick note on my above script, the string does not have to be converted to datetime, it's implicity performed (run the script).

Everett Wilson
ewilson10@yahoo.com

iLoveSQL SSCrazy Points: 2888 More actions · Answer 7

Hi Everett,

I don't mean to rude or anything so forgive me if I ask this question.

Is there a reason that you set the data type for year, month and day as varchar(50)? Shouldn't that be varchar(2) for month and day and varchar(4) for year if you really don't want to over use of allocated memeory?

I often tell developers not to abuse varchar only because they can. 🙂

To be more performance intensive tuning, I'd recommend to use char(4) for year and optional char(2) for day and month and add '0' in front.

BTW, yes I agree about implicity performed. I saw use this kind of query from Data Warehouse date phrasing..

Please feel free to comment on it.. I'd like to hear about this kind of thing often.

Thank you

JacekO SSCertifiable Points: 6610 More actions · Answer 8

The problem of using CHAR type in this case is that then the concatenated string will look like this ( taking Everett's example):

9 /13/2004 - note the space after 9

And although the convertion to DATETIME works fine in this case sometimes you may want avoid using CHAR type and use VARCHAR when dealing with string concatenation.

And of course there is no need to use VARCHAR(50) in his example... but I leave the explanation of this to him

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 9

Hello. Usually when I do a onetime shot I jus throw in a variable that I don't have to think about, using estimation as a guide (50,500,1000,2000,5000).

Good point about avoiding the char in this case since the datepart does not include a leading zero:

print datepart(mm,getdate())

Everett Wilson
ewilson10@yahoo.com

iLoveSQL SSCrazy Points: 2888 More actions · Answer 10

Right Jacek, I've seen so many times many developers made that kind of mistake about CHAR w/ space. I only uses for rare case that needs absolute performance gain and isolated enviroment w/ standarized the format so I'd would add '0' in front if there is only letter exists.

So many times, varchar(2) is better for this case.

iLoveSQL SSCrazy Points: 2888 More actions · Answer 11

Hi Everett,

I thought so, I'd do that all the time.. ^^;;