filtering by date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

filtering by date

Author

Topic

twocans
Starting Member

6 Posts

Posted - 2008-06-13 : 12:30:52

Hello I am trying to gilter a table by getdate() (i also tried now()) but I cannot seem to be able to do it I place my code below if anyone can help. am grateful, my db is sql 2005.

<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = getdate()
If (Request("MM_EmptyValue") <> "") Then
Recordset1__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_connpeepeek_STRING
Recordset1_cmd.CommandText = "SELECT usr_image1, dateimage_usr FROM diddle.ps_usr_image WHERE dateimage_usr = ?"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 135, 1, -1, Recordset1__MMColParam) ' adDBTimeStamp

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>

if any one can help I would be grateful

k

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 12:40:02

GETDATE() will return timepart also. You need to strip off time part before comparison if you table contains only date value. Use DATEADD(dd,DATEDIFF(d,0,GETDATE()),0) to strip off time part. You dont need parameter. You can directly pass it to query

SELECT usr_image1, dateimage_usr FROM diddle.ps_usr_image WHERE dateimage_usr =DATEADD(dd,DATEDIFF(d,0,GETDATE()),0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-13 : 13:27:48

SELECT usr_image1, dateimage_usr FROM diddle.ps_usr_image
WHERE dateimage_usr >=DATEADD(day,DATEDIFF(day,0,GETDATE()),0) and dateimage_usr <DATEADD(day,DATEDIFF(day,0,GETDATE())+1,0)

Madhivanan

Failing to plan is Planning to fail

twocans
Starting Member

6 Posts

Posted - 2008-06-13 : 13:43:46

Thank you gentlemen I have tried what you gave but I get what i was getting earlier.

Application uses a value of the wrong type for the current operation.

/a.asp, line 19

regards

k

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 13:46:59

quote:
Originally posted by twocans

Thank you gentlemen I have tried what you gave but I get what i was getting earlier.

Application uses a value of the wrong type for the current operation.

/a.asp, line 19

regards

k

Have you replaced your query with one Madhi provided for the value of Recordset1_cmd.CommandText?

twocans
Starting Member

6 Posts

Posted - 2008-06-13 : 14:09:55

thank youn i tried that too only to get the error

Application uses a value of the wrong type for the current operation.

i have got logmein if u want an invite to my pc hehe

k

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 14:19:56

http://classicasp.aspfaq.com/date-time-routines-manipulation/could-i-get-a-little-help-with-dates.html

twocans
Starting Member

6 Posts

Posted - 2008-06-14 : 04:46:13

Thank you Visha,

I read this "You should try your best to use YYYYMMDD format wherever possible" The date was origanlly entered into my db by using getdate(). when I look at it now in my db column using sqp manager, i see it as mm//dd/yyyy time, I am a little confused, am i only seeing it like that because possibly the tool i use to see it with is reformating the contant for display porpus. Dates are a nightmare thanks for help

k

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-14 : 04:50:16

quote:
Originally posted by twocans

Thank you Visha,

I read this "You should try your best to use YYYYMMDD format wherever possible" The date was origanlly entered into my db by using getdate(). when I look at it now in my db column using sqp manager, i see it as mm//dd/yyyy time, I am a little confused, am i only seeing it like that because possibly the tool i use to see it with is reformating the contant for display porpus. Dates are a nightmare thanks for help

k

how did it come like mm//dd/yyyy? its not a proper date format.b/w is dateimage_usr a datetime field in your table?

twocans
Starting Member

6 Posts

Posted - 2008-06-14 : 05:39:20

Thank you Visakh16

[dateimage_usr] datetime CONSTRAINT [DF__ps_usr_im__datei__59063A47] DEFAULT getdate() NULL,

that is how the date is entered into my database.

when i open ems sql manager and see data in my data base I see the date as dd/MM/yyyy, on having a look at the ems controls which i used to help me build my sql db I see under date time format dd/MM/yyyy hh:mm , can you advise me, should i have it so the YYYYMMDD ?? how wuld i have the time. also no matter what db i am using in the future should this be set as my standard.

regards and thank you

k

ps i have to pop out and do shopping i will be back in a few hours am grateful for all help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-14 : 10:58:42

quote:
Originally posted by twocans

Thank you Visakh16

[dateimage_usr] datetime CONSTRAINT [DF__ps_usr_im__datei__59063A47] DEFAULT getdate() NULL,

that is how the date is entered into my database.

when i open ems sql manager and see data in my data base I see the date as dd/MM/yyyy, on having a look at the ems controls which i used to help me build my sql db I see under date time format dd/MM/yyyy hh:mm , can you advise me, should i have it so the YYYYMMDD ?? how wuld i have the time. also no matter what db i am using in the future should this be set as my standard.

regards and thank you

k

ps i have to pop out and do shopping i will be back in a few hours am grateful for all help.

SQL Server stores datetime fields in format yyyy-mm-dd hh:mm:ss.xyz

GETDATE() function also returns the date in the same format.
But if we are storing the date values alone in db the time part will be 00:00:00.000. So for comparison to work correctly we need to strip off time part from GETDATE() value which is done by

DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

twocans
Starting Member

6 Posts

Posted - 2008-06-16 : 06:36:15

THank you very much all. This was soo soo helpful.

best wishes

k

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources