Access to SQL Server-Date functions

  • Thread starter Thread starter Arch
  • Start date Start date
A

Arch

I am migrating from Access 2000 to SQL Server. Please help
with my Access Query which I am translating into T-SQL for
a SQL Server Stored Procedure. I am having trouble with
the Date and Time functions.

The SQL in Access 2000 looks like this:
WHERE
((Agreements.AgreementDate)<Now()-275-[AwardExt]*31
AND (Agreements.AgreementDate)>Now()-365-[AwardExt]*31));

I need the results to be displayed only in dates in this
mm/dd/yyyy format and not in mm/dd/yyyy 00:00:00.000
format as I do not want the time displayed.
I am not being able to get the syntax right. Please help
and thanks much in advance.
Arch
 
Hi,
For display purposes, use the CONVERT function with a style of 101.
Look up the function in Books on line.

HTH
Dan Artuso, MVP
 
Thank You for your reply. I have already been through
neumerous material but I don't seem to get the syntax
right. I keep getting errors.
Can someone please help me with the right syntax. I am new
to programming and I appreciate the help in advance.
Thanks
Arch
-----Original Message-----
Hi,
For display purposes, use the CONVERT function with a style of 101.
Look up the function in Books on line.

HTH
Dan Artuso, MVP

"Arch" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
I am migrating from Access 2000 to SQL Server. Please help
with my Access Query which I am translating into T-SQL for
a SQL Server Stored Procedure. I am having trouble with
the Date and Time functions.

The SQL in Access 2000 looks like this:
WHERE
((Agreements.AgreementDate)<Now()-275-[AwardExt]*31
AND (Agreements.AgreementDate)>Now()-365-[AwardExt] *31));

I need the results to be displayed only in dates in this
mm/dd/yyyy format and not in mm/dd/yyyy 00:00:00.000
format as I do not want the time displayed.
I am not being able to get the syntax right. Please help
and thanks much in advance.
Arch
Click to expand...


.
Click to expand...
 
Hi,
Okay, here's how I would select a style to display one of my date fields:
Select Convert(varChar,InventoryDate,101) from Machines

I would then see the results displayed as mm/dd/yyy

HTH
Dan Artuso, MVP

Arch said:
Thank You for your reply. I have already been through
neumerous material but I don't seem to get the syntax
right. I keep getting errors.
Can someone please help me with the right syntax. I am new
to programming and I appreciate the help in advance.
Thanks
Arch
-----Original Message-----
Hi,
For display purposes, use the CONVERT function with a style of 101.
Look up the function in Books on line.

HTH
Dan Artuso, MVP

"Arch" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
I am migrating from Access 2000 to SQL Server. Please help
with my Access Query which I am translating into T-SQL for
a SQL Server Stored Procedure. I am having trouble with
the Date and Time functions.

The SQL in Access 2000 looks like this:
WHERE
((Agreements.AgreementDate)<Now()-275-[AwardExt]*31
AND (Agreements.AgreementDate)>Now()-365-[AwardExt] *31));

I need the results to be displayed only in dates in this
mm/dd/yyyy format and not in mm/dd/yyyy 00:00:00.000
format as I do not want the time displayed.
I am not being able to get the syntax right. Please help
and thanks much in advance.
Arch
Click to expand...


.
Click to expand...
Click to expand...
 
Arch,

I don't think T-SQL has a NOW() function. It's been a while, but I think
you need to use GetSysDate() or something like that.

HTH
Dale
 
Actually, I believe it's GetDate()

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Dale Fye said:
Arch,

I don't think T-SQL has a NOW() function. It's been a while, but I think
you need to use GetSysDate() or something like that.

HTH
Dale

Arch said:
I am migrating from Access 2000 to SQL Server. Please help
with my Access Query which I am translating into T-SQL for
a SQL Server Stored Procedure. I am having trouble with
the Date and Time functions.

The SQL in Access 2000 looks like this:
WHERE
((Agreements.AgreementDate)<Now()-275-[AwardExt]*31
AND (Agreements.AgreementDate)>Now()-365-[AwardExt]*31));

I need the results to be displayed only in dates in this
mm/dd/yyyy format and not in mm/dd/yyyy 00:00:00.000
format as I do not want the time displayed.
I am not being able to get the syntax right. Please help
and thanks much in advance.
Arch
Click to expand...
Click to expand...
 
Back
Top