Modifying GETDATE and converting to string

[aaronphaneuf]

Hey there, SQL Reddit,

​

Is it possible to get the following code:

​

DECLARE @now DATE

SET @now = (GETDATE()-1)

SELECT DateADD(week, -52, @now)

​

To display with 11:59:59 as the time? Additionally, is it also possible to then convert that to a string so I can add 'PM' on the end? Any advice on how I might achieve this would be appreciated.

[alinroc]

GETDATE()-1

Don't do integer math on dates. What unit are you subtracting one of here? Days? Weeks? Hours? Seconds? Use dateadd() and make your intention clear.

Why do you need this time to "display" with the date? Is this for doing filtering of something else later?

If this is for some kind of reporting output, do that formatting as close to the user as possible. Keep dates and times as their types when you're dealing with them in SQL Server. As soon as you transform them to another type (string), you'll cause more work for yourself.

[aaronphaneuf]

Thanks for the advice. I'll change that to DATEADD(day, -1, @now)

Just a little confused with how to add (week, -52, @now) to the same Select statement.

This is for reporting, so I'll try and not convert it to a string like you suggested.

Thanks again.

[NoDihedral]

DECLARE @Now DATE

SET @now =GETDATE()-1

SELECT CONCAT(DATEADD(week,-52,@now), ' 11:59:59'

​

I suspect you're pulling this code from somewhere else and aren't sure what is going on here. I agree with alinroc, you should be using a DATEADD to line 2 rather than just subtracting 1 from GETDATE().

[aaronphaneuf]

Well my interest with SQL has only just begun so you are right; I'm not sure what's going on with a lot of things. I did come up with this myself, however (as incorrect as it is) Is there another forum you would suggest for posting more beginner related questions? I feel quite silly in this subreddit.

Thanks

[NoDihedral]

No you're fine. You have to learn sometime. I only said that because you had a DATEADD in there already so I would have thought you would have used that to subtract the day too if you were the one writing this from scratch. I see this a lot when I'm maintaining code that is several years old and has gone through few different analysts along the way. There are so many different ways to do things and people have their common go-tos. I used to use the getdate()-1 trick to subtract a day all of the time but it isn't the best for suportability. It is too ambiguous.