Get Date Format as Date

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Get Date Format as Date

Post by adam »

Hi anyone,
I have the following SQL Query where I’m collecting data from three tables from my SQL database. I’m using version 2005 SQL server.

Code: Select all

strSQL = "SELECT Main.BilledDate, ItemDetail.ItemDescription,ctInfo.Sex FROM ((Main INNER JOIN ItemDetail On Main.BillID =" & _
            "ItemDetail.BillNo)INNER JOIN ctInfo On Main.PersonID = ctInfo.ctIdxNo) WHERE FORMAT (Main.BilledDate,'dd-MM-yyyy')= " & ws.Range("L8").Value
            rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
            ws.Range("E18").CopyFromRecordset rst
            rst.Close
However, when I run the code I'm getting the error "'FORMAT' is not a recognized built-in function name". is it because I'm using an older version of SQL server? if so is there any possibilities I can change the code accordingly? I want to filter data by date.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get Date Format as Date

Post by HansV »

That's correct - FORMAT was introduced in SQL Server 2012. Change

FORMAT (Main.BilledDate,'dd-MM-yyyy')

to

CONVERT(VARCHAR(10),Main.BilledDate,105)
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get Date Format as Date

Post by adam »

I'm getting the error "conversion failed when converting the varchar value '31-10-2012' to data type int". in my sql table the date field with date and time. I only want the data to get filtered by date only.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get Date Format as Date

Post by HansV »

What does ws.Range("L8") contain? A date value or a text value?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get Date Format as Date

Post by adam »

it contains a date value.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get Date Format as Date

Post by HansV »

Does this work?

Code: Select all

strSQL = "SELECT Main.BilledDate, ItemDetail.ItemDescription,ctInfo.Sex FROM ((Main INNER JOIN ItemDetail On Main.BillID =" & _
            "ItemDetail.BillNo)INNER JOIN ctInfo On Main.PersonID = ctInfo.ctIdxNo) WHERE CONVERT(VARCHAR(10),Main.BilledDate,105)='" & FORMAT(ws.Range("L8").Value, "dd\-mm\-yyyy") & "'"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get Date Format as Date

Post by adam »

Yeah it worked fine. Thanks for the help Hans. I've added a parameter to the code so that it would filter a certain text from the column. When I add only one search parameter the code works fine. But if I add multiple parameters using "OR" it filters all the data from that parameter.

How could this be solved?

Code: Select all

strSQL = "SELECT Main.BilledDate, ItemDetail.ItemDescription,ctInfo.Sex FROM ((Main INNER JOIN ItemDetail On Main.BillID =" & _
            "ItemDetail.BillNo)INNER JOIN ctInfo On Main.PersonID = ctInfo.ctIdxNo) WHERE CONVERT(VARCHAR(10),Main.BilledDate,105)='" & FORMAT(ws.Range("L8").Value, "dd\-mm\-yyyy") & "' AND ItemDetail.ItemDescription = 'Apple' OR ItemDetail.ItemDescription = '(Orange'"
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get Date Format as Date

Post by HansV »

AND has priority over OR, so you must use parentheses ( ) around the OR part:

Code: Select all

strSQL = "SELECT Main.BilledDate, ItemDetail.ItemDescription,ctInfo.Sex FROM ((Main INNER JOIN ItemDetail On Main.BillID =" & _
            "ItemDetail.BillNo) INNER JOIN ctInfo On Main.PersonID = ctInfo.ctIdxNo) WHERE CONVERT(VARCHAR(10),Main.BilledDate,105)='" & FORMAT(ws.Range("L8").Value, "dd\-mm\-yyyy") & "' AND (ItemDetail.ItemDescription = 'Apple' OR ItemDetail.ItemDescription = 'Orange')"
Instead of OR, you can use an IN clause:

Code: Select all

strSQL = "SELECT Main.BilledDate, ItemDetail.ItemDescription,ctInfo.Sex FROM ((Main INNER JOIN ItemDetail On Main.BillID =" & _
            "ItemDetail.BillNo) INNER JOIN ctInfo On Main.PersonID = ctInfo.ctIdxNo) WHERE CONVERT(VARCHAR(10),Main.BilledDate,105)='" & FORMAT(ws.Range("L8").Value, "dd\-mm\-yyyy") & "' AND ItemDetail.ItemDescription IN ('Apple','Orange')"
This option is especially attractive if you want to specify more than two options.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get Date Format as Date

Post by adam »

Thankyou so much Hans. It worked perfect.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get Date Format as Date

Post by adam »

I'm trying to sum up the word "Apple" from the selected date by gender. But I'm getting the error "incorrect syntax near '='"

Code: Select all

 strSQL = "SELECT -SUM(ItemDetail.ItemDescription]='Apple') " & _
        "FROM ((Main INNER JOIN ItemDetail On Main.BillID =" & _
        "ItemDetail.BillNo)INNER JOIN ctInfo On Main.PersonID = ctInfo.ctIdxNo) WHERE CONVERT(VARCHAR(10),Main.BilledDate,105)=" & _
        "'" & Format(ws.Range("L8").Value, "dd\-mm\-yyyy") & " GROUP BY [ItemDetail.ItemDescription] PIVOT ([ctInfo.Sex]) In " & _
            "(Male, Female)"
        rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
        ws.Range("E18").CopyFromRecordset rst
        rst.Close
What am I doing wrong in here?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get Date Format as Date

Post by HansV »

Remove the ] after ItemDescription
Insert a space after ItemDetail.BillNo)
Insert a single quote after the " in " GROUP BY
Remove the brackets from ItemDetail.ItemDescription
Remove the brackets and parentheses from ctInfo.Sex
Add single quotes around Male and Female

Code: Select all

    strSQL = "SELECT -SUM(ItemDetail.ItemDescription='Apple') " & _
        "FROM ((Main INNER JOIN ItemDetail On Main.BillID = " & _
        "ItemDetail.BillNo) INNER JOIN ctInfo On Main.PersonID = ctInfo.ctIdxNo) WHERE CONVERT(VARCHAR(10),Main.BilledDate,105)=" & _
        "'" & Format(ws.Range("L8").Value, "dd\-mm\-yyyy") & "' GROUP BY ItemDetail.ItemDescription PIVOT ctInfo.Sex In " & _
        "('Male', 'Female')"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get Date Format as Date

Post by adam »

Thanks for the help. But I'm still getting the same error? anything is missed in the code?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get Date Format as Date

Post by HansV »

That's impossible for me to tell without seeing the database, but since it's SQL server you cannot attach it. Sorry!

You might experiment by starting with a shorter SQL. Does this work?

Code: Select all

    strSQL = "SELECT -SUM(ItemDetail.ItemDescription='Apple') " & _
        "FROM ItemDetail " & _
        "GROUP BY ItemDetail.ItemDescription"
If that works, try

Code: Select all

    strSQL = "SELECT -SUM(ItemDetail.ItemDescription='Apple') " & _
        "FROM ((Main INNER JOIN ItemDetail On Main.BillID = " & _
        "ItemDetail.BillNo) INNER JOIN ctInfo On Main.PersonID = ctInfo.ctIdxNo) " & _
        "GROUP BY ItemDetail.ItemDescription"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get Date Format as Date

Post by adam »

Let me give it a try.
Best Regards,
Adam