Access to SQL Server-Date functions

[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

 

[Dan Artuso]

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]

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

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

.

 

[Dan Artuso]

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

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

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

.

 

[Dale Fye]

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

 

[Douglas J. Steele]

Actually, I believe it's GetDate()

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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