Need to add AM PM to a datestamp

Question

Post reply

Need to add AM PM to a datestamp

tmiller 90290

Valued Member

Points: 72
More actions
August 31, 2016 at 10:53 am

#329706

New here, so if I did not follow a format, my bad.
I have a query that I am using @startdate and @enddate.
DECLARE @StartDate SMALLDATETIME
DECLARE @EndDate SMALLDATETIME
SET @StartDate = '2016-08-30 08:00:00.000'
SET @EndDate = '2016-08-30 11:59:59.997'
WHERE (ORDERS.OrderReceivedDate >= @StartDate)
AND (ORDERS.OrderReceivedDate < @EndDate)
How do I add AM or PM for the time?
Regards,

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You don't, you use 24 hours.

So, these are AM:

'2016-08-30 08:00:00.000'

'2016-08-30 11:59:59.997'

And these are PM:

'2016-08-30 20:00:00.000'

'2016-08-30 23:59:59.997'

Also, you need to take in account that seconds will always be zero(:00) for smalldatetime and values will be rounded to the closest minute.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tmiller 90290 Valued Member Points: 72 More actions · Answer 2

so don't use the smalldatetime?

or change the format to the 24 hr?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

If you want precision sub-minute - then you need to not use smalldatetime. per BOL:

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds [highlight="#ffff11"]always zero (:00) and without fractional seconds[/highlight]

If you're okay with the precision - then then 24 hour notation is baked in by default. It's stored as a numeric value anyway, so the AM/PM vs 24-hour notation is a formatting concern.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

tmiller 90290 Valued Member Points: 72 More actions · Answer 4

gotcha, for me I have no problem with the 24 clock, I understand it, my boss however, well, she wants to see the AM/PM, lol. I will just throw it in excel and format and send. 🙂

Smendle Hall of Fame Points: 3969 More actions · Answer 5

tmiller 90290 (8/31/2016)
gotcha, for me I have no problem with the 24 clock, I understand it, my boss however, well, she wants to see the AM/PM, lol. I will just throw it in excel and format and send. 🙂

If you want to convert date values to return AM/PM then use CAST or CONVERT to nvarchar.

straight from MSDN....

SELECT

GETDATE() AS UnconvertedDateTime,

CAST(GETDATE() AS nvarchar(30)) AS UsingCast,

CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ;

GO

**Edit I changed the last CONVERT line to 100 to test something and forgot to change it back to the ISO8601 value of 126***