Results 1 to 11 of 11

Thread: Date Only in SQL 2K [Resolved]

  1. #1

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Date Only in SQL 2K [Resolved]

    I need to run a query where the where clause does a date comparison,
    but the field in the db is a datetime field so it doesn't match. I
    tried using CAST and parsing out the date parts using Month(),
    Day(), Year(). Just need to match something like this...

    Code:
    SELECT * FROM Table1 WHERE Start = '09/13/2004'
    Start is a DateTime field with the times in it too. Don't know why
    this isn't working. Maybe it still has the time on the end of it? Its
    supossed to be simple. Guess its time to go home.

    Last edited by RobDog888; Sep 15th, 2004 at 12:04 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2
    PowerPoster BruceG's Avatar
    Join Date
    May 2000
    Location
    New Jersey (USA)
    Posts
    2,628
    You could do something like this:
    Code:
    SELECT * FROM Table1 
    WHERE Start >= '09/13/2004' AND Start < '09/14/2004'
    "It's cold gin time again ..."

    Check out my website here.

  3. #3

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713
    I did that and it didnt work either. Thats why I started trying the
    date/time parsing.

    Code:
    SELECT * FROM Table1 WHERE Month(Start) + '/' + Day(Start) + '/' + Year(Start) = '9/13/2004'
    Still no match and generates an error converting '/' to an int type?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174
    First try this:

    Code:
    SELECT * FROM Table1 WHERE Start = #09/13/2004#


    I personally use a DateDiff() comparison, so that it takes care of times in the field.

    Code:
    ... WHERE DATEDIFF("d",[Start],'09/13/2004') = 0
    But then, I didn't read your post carefully and don't know what db you're using.

  5. #5
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758
    Another option is to use the Convert function (SQL Server only)

    Where Convert(varchar(10), Start, 101) = '09/13/2004'

  6. #6
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343
    Code:
    SELECT * FROM Table1 WHERE Start between #13 Sep 2004 00:00:00# and #13 Sep 2004 23:59:59#
    Perhaps?
    You add in the date part ofcourse


    Vince

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882
    Originally posted by brucevde
    Another option is to use the Convert function (SQL Server only)

    Where Convert(varchar(10), Start, 101) = '09/13/2004'
    I would always use this method, unless an INDEX is being scanned.

    Then I would try something like:

    START>='2004-09-13' AND START<'2004-09-14'

    Not sure if that would work - but it makes sense that it might...

  8. #8

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713
    I am going to try
    VB Code:
    1. Convert(varchar(10), Start, 101) = '09/13/2004'
    2. 'Or
    3. DATEDIFF("d",[Start],'09/13/2004') = 0
    4. 'Or
    5. START>='2004-09-13' AND START<'2004-09-14'
    They all three look good. Guess I was just too !@#$! tired.

    Thanks, if it works for me at work tomorrow I will post an update.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  9. #9

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713
    It looks like Convert is the solution I am going to use. It works fine now!

    Thanks for all the help guys.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882
    Convert is a great solution, just keep in mind that if the DATE field is an index, wrapping a function around it will cause a table scan vs. an index scan. This will effect speed - obviously...

  11. #11

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713
    I will keep that in mind and if it looks like that is happening then
    I will change to this format.

    VB Code:
    1. START >= '2004-09-13' AND START < DateAdd('d',1, '2004-09-13')
    Thanks.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width