| Author |
 Topic  |
|
|
Bookie
Average Member
USA
856 Posts |
 Posted - 16 October 2003 : 11:33:40
|
Is it possible to use a formatdatetime function on data in a querystring? I'm not talking about the WHERE clause but I'm talking about the fields you're designating to select. More specifically, I have a text field that holds date & time but I need to basically strip away the time and select distince dates. Is this possible? Here's an example of the string I'm trying. I've played with it but I can't get it to work. The field I'm trying to select is called Date_stamp.
strSQL = "SELECT DISTINCT tbljournal.FormatDateTime(Date_stamp, vbshortdate) FROM tblJournal ORDER BY Date_stamp DESC;"
I really don't know if it's possible and, if it is, how I would format it to work correctly. Any help would be appreciated! |
Participate in my nonsense |
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 16 October 2003 : 14:19:52
|
FormatDateTime is a VBScript function not a SQL function, so it can't be used the way you are trying to.
I can see what you want to do, but can't think of the syntax to do it  |
 |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 16 October 2003 : 14:32:30
|
Use CONVERT, e.g.
strSQL = "SELECT DISTINCT CONVERT(datetime,Date_stamp,101) FROM tblJournal ORDER BY Date_stamp DESC;"
(code 101 will result in mm/dd/yyyy date format (USA), more codes available in SQL Server Books Online) |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - D3mon on 16 October 2003 14:34:03 |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 16 October 2003 : 21:08:57
|
Some other helpful info would be if you only want to select dates that have a give month, day, or year
DAY
strSql = strSql & " WHERE DATEPART(day, DateField)='" & day(Now) & "'"
MONTH
strSql = strSql & " WHERE DATEPART(month, DateField)='" & month(Now) & "'"
YEAR
strSql = strSql & " WHERE DATEPART(year, DateField)='" & year(Now) & "'"
|
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 16 October 2003 : 21:19:59
|
| Will DatePart work on text fields though ?, from what I've read the second argument to DatePart must be a date expression. |
|
|
|
Bookie
Average Member
USA
856 Posts |
Posted - 16 October 2003 : 21:41:34
|
quote:
Originally posted by D3mon
Use CONVERT, e.g.
strSQL = "SELECT DISTINCT CONVERT(datetime,Date_stamp,101) FROM tblJournal ORDER BY Date_stamp DESC;"
(code 101 will result in mm/dd/yyyy date format (USA), more codes available in SQL Server Books Online)
I'll give this a shot and let you know how it goes. Thanks! |
Participate in my nonsense |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 16 October 2003 : 21:57:54
|
| OK, so I would go with the Convert function and not the DatePart function. |
|
|
|
Bookie
Average Member
USA
856 Posts |
Posted - 16 October 2003 : 22:01:14
|
I'm getting the following error but I'm trying to track it down.
Microsoft JET Database Engine error '80040e14'
Undefined function 'CONVERT' in expression. |
Participate in my nonsense |
|
|
|
Bookie
Average Member
USA
856 Posts |
Posted - 16 October 2003 : 23:10:03
|
| Actually, I just thought of something. I'm not using SQL server but an Access database. Would it be any different. By the way, I've been searching and trying stuff but I can't get it to work. I'll keep trying and searching. |
Participate in my nonsense |
|
|
|
Bookie
Average Member
USA
856 Posts |
Posted - 17 October 2003 : 00:57:56
|
Holy cow! That was a pain in the butt!
strSQL = "SELECT DISTINCT CDate(Format([tblJournal].date_stamp,'mm/dd/yyyy')) as date_stamp FROM tblJournal;"[/CODE]
Thanks everyone for pointing me in a direction to figure out the solution which, by the way, was not easy to find. I guess I was looking for the cdate function rather than the convert function. Oh well, it's done now. |
Participate in my nonsense |
|
|
| |
Topic |
|
Topic Locked
