We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Converting Code from Access to SQL -- Specifically DateSerial

PHS_IT
PHS_IT asked
on
Medium Priority
313 Views
Last Modified: 2012-05-05
I am having trouble converting a bit of Access code that appears in my select statement as:

SELECT DateSerial(Year(dbo_TPB105_CHARGE_DETAIL!chg_srv_ts),Month(dbo_TPB105_CHARGE_DETAIL!chg_srv_ts),Day
(dbo_TPB105_CHARGE_DETAIL!chg_srv_ts)) AS cntdate

And also in the WHERE statement:

WHERE (((DateSerial(Year([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts]),Month([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts]),Day([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts])))>Date()-2 And (DateSerial(Year([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts]),Month([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts]),Day([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts])))<Date()))

What I am trying to accomplish is to obtain the records where the chg_srv_ts is yesterday (between 12:00 a.m. and 11:59 p.m.)

Can anyone help?

Thanks!



Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010
Commented:
Hi PHS_IT,

SELECT CONVERT(datetime, CONVERT(varchar(12), dbo.TPB105_CHARGE_DETAIL.chg_srv_ts, 106)) AS cntdate

...

WHERE CONVERT(datetime, CONVERT(varchar(12), dbo.TPB105_CHARGE_DETAIL.chg_srv_ts, 106)) =
    (CONVERT(datetime, CONVERT(varchar(12), GETDATE(), 106)) - 1)

Regards,

Patrick

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
SELECT CAST(CONVERT(nvarchar(10), chg_srv_ts,112) as datetime)  as cntdate
in the WHERE statement

WHERE chg_srv_ts BETWEEN dateadd(day,-1 ,CAST(CONVERT(nvarchar(10),getdate(),112) as datetime)) AND CAST(CONVERT(nvarchar(10),getdate(),112) as datetime)

the where will always return yesterdays data
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.