DAY() not working with 'Left Join'?

I have two tables. Days (1-31) and dates (random dates)

If I have a query that is

Select Day, Date

From days LEFT JOIN dates ON days.Day = DAY(dates.date)

Order By Day, Date

The left join will not return all the days in days just the ones that join with dates. It returns as if I am doing and 'Inner join'. What do I need to do different?

Thanks.

ry this:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

|||That didn't seem to do anything. What was the thought behind this if you don't mind?|||

CREATE TABLE [dbo].[Dates]([Date] [datetime] NULL,

[id] [int] NULL)

INSERT INTO [Dates] ([Date],[id])VALUES('Oct 2 2006 12:00:00:000AM',1)
INSERT INTO [Dates] ([Date],[id])VALUES('Oct 4 2006 12:00:00:000AM',2)

CREATE TABLE [Days]([Day] [int] NULL)

INSERT INTO [Days] ([Day])VALUES(1)
INSERT INTO [Days] ([Day])VALUES(2)
INSERT INTO [Days] ([Day])VALUES(3)
INSERT INTO [Days] ([Day])VALUES(4)
INSERT INTO [Days] ([Day])VALUES(5)
INSERT INTO [Days] ([Day])VALUES(6)
INSERT INTO [Days] ([Day])VALUES(7)
INSERT INTO [Days] ([Day])VALUES(8)
INSERT INTO [Days] ([Day])VALUES(9)
INSERT INTO [Days] ([Day])VALUES(10)
INSERT INTO [Days] ([Day])VALUES(11)
INSERT INTO [Days] ([Day])VALUES(12)
INSERT INTO [Days] ([Day])VALUES(13)
INSERT INTO [Days] ([Day])VALUES(14)
INSERT INTO [Days] ([Day])VALUES(15)
INSERT INTO [Days] ([Day])VALUES(16)
INSERT INTO [Days] ([Day])VALUES(17)
INSERT INTO [Days] ([Day])VALUES(18)
INSERT INTO [Days] ([Day])VALUES(19)
INSERT INTO [Days] ([Day])VALUES(20)
INSERT INTO [Days] ([Day])VALUES(21)
INSERT INTO [Days] ([Day])VALUES(22)
INSERT INTO [Days] ([Day])VALUES(23)
INSERT INTO [Days] ([Day])VALUES(24)
INSERT INTO [Days] ([Day])VALUES(25)
INSERT INTO [Days] ([Day])VALUES(26)
INSERT INTO [Days] ([Day])VALUES(27)
INSERT INTO [Days] ([Day])VALUES(28)
INSERT INTO [Days] ([Day])VALUES(29)
INSERT INTO [Days] ([Day])VALUES(30)
INSERT INTO [Days] ([Day])VALUES(31)

And the script that works:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

If you cannot run this, let's see what is the problem again.

|||

So I get to playing around with your example and descovered some stuff I didn't know about left joins.

My query has touble when I add a Where clause on it to filter dates to a certain range. The differents querys are below in case someone else needs help. Thanks.

NOT WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date)

Where b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date) and

b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

|||

If you use a subquery with a where clause fro your LEFT JOIN, it should work.

Select a.Day, b.date

From days a LEFT JOIN (select * FROM dateshiftcrewTable Where date Between '1/1/1999' and '4/4/1999') b ON a.Day = DAY(b.date)

Order By a.Day, b.date

David Portas on Any one? Query Help Please

Thanks for your response.
I got that query working,
SELECT P.productname, COUNT(S.productname)
FROM Products AS P
LEFT JOIN Sales AS S
ON P.productname = S.productname
GROUP BY P.productname ;
but what if there was another column CustomerId, which exists in both tables
and I want to just see products just for a particular customer?
So if a product is offered to a customer , but he doesn't order it then in
result I want to show that product with count as 0...Try this:
SELECT P.productname, COUNT(S.productname)
FROM Products AS P
LEFT JOIN Sales AS S
ON P.productname = S.productname
AND P.customerid = S.customerid
WHERE P.customerid = /* specify the required customer */
GROUP BY P.productname ;
The best way to get help with a problem such as this is to post DDL and
sample data. The following artcile explains how:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thnx David,
It worked for me.
"David Portas" wrote:

> Try this:
> SELECT P.productname, COUNT(S.productname)
> FROM Products AS P
> LEFT JOIN Sales AS S
> ON P.productname = S.productname
> AND P.customerid = S.customerid
> WHERE P.customerid = /* specify the required customer */
> GROUP BY P.productname ;
> The best way to get help with a problem such as this is to post DDL and
> sample data. The following artcile explains how:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>sql

DATETIME To Format mm/dd/yyyy hh:mm am/pm

I have a column in a database set as a DATETIME datatype, when I select it, I want to return it as:

mm/dd/yyyy hh:mm am or pm.

How in the world can I do this? I looked at the function CONVERT() and it doesnt seem to have this format as a valid type. This is causing me to lose my hair, in MySQL it is just so much easier. .

At any rate, currently when I select the value without any convert() it returns as:

June 1 2007 12:23AM

Which is close, but I want it as:

06/01/2007 12:23AM

Thanks!

Hi,

Normally you would be formatting your date on the UI or Report side, which means you must let your program display it correctly or let your reporting engine format your date as you want to.

But if you really want to change the format of your date, you can do this by changing the type to string by using the CONVERT function. The closest that I can come up with is this format:

mm/dd/yyyy

checki it here (code 101):

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

the syntax would be like this in SQL

SELECT CONVERT('your date', nvarchar(MAX), 101)

Maybe you can mix it up with code 108 so that you can concatenate the time with it.

cheers,

Paul June A. Domag

|||

The reason I am formatting it in SQL is this is inside a trigger written in pure SQL which emails from the DB.

Yeah CONVERT() using type 101 was the closest I could get as well, but it only displays date, no time. I need both date and time in the format I specified above.

This is absolutley stupid that they did it this way, they should take a lession from MySQL which allows you to format a DATETIME in any fashion via strings such as %m/%d/%Y etc, etc.

Anybody have other ideas?

|||

Hi,

Why not just combine the codes 101 and 108 and maybe manually parse it using substring?

You can create a scalar function to make it reusable.

Code Snippet

DECLARE @.dt VARCHAR(MAX)

SELECT @.dt = CONVERT(nvarchar(MAX), GETDATE(), 101) + ' ' + CONVERT(nvarchar(MAX), GETDATE(), 108)

-- After this just use Substring to satisfy your formatting

note: varchar(max) is only available in SQL2005. specify the lenght if your using SQL2000

cheers,

Paul June A. Domag

|||

As Paul indicated, formating is normally left to the client application. I suspect the developer you are working with either does not know how to properly format for display in his/her application, or is too lazy and is passing the responsibility off to the database.

This expression should provide the date in the form you desire. Replace the [ @.MyDate ] with your column or date value. You can easily create your own function that will do this for you so that you can re-use this expression.

DECLARE @.MyDate datetime

SET @.MyDate = '2007/07/21 11:35:45.255PM'

SELECT MyDate =
convert( varchar(10), @.MyDate, 101) +
stuff( right( convert( varchar(26), @.MyDate, 109 ), 15 ), 7, 7, ' ' )

MyDate
-
07/21/2007 11:35 PM

|||

Arnie Rowland ,

You are the man, that worked like a charm. I guess my problem with the convert function is that there is no predefined format of:

mm/dd/yyyy hh:mm am/pm

I would assume this is very very popular, so I am confused as to why it is not implemented. As far as this benig done on the front end, this has to be done at the DB level, since we send emails out via the database.

|||

I have to say that I wouldn't want to send an email from a trigger that required any kind of special formatting. Perhaps an alert to a sysadmin, but if I was going to send correspondence like that, I would put my information in a queue of some sort and have a tool to send the email.

the CONVERT thing is a mess because it doesn't give you enough formats, unlike a proper data presentation layer would. I have (in the past) used datePart to build up a date formatter of my own, or you could probably do it with the CLR quite nicely. But SQL Server should be used to manage and manipulate data, not format it (as a broad rule of course. We all do it from time to time to appease a user/manager/programmer etc, so don't think I am saying it is horrible, it just isn't as ideal as using a programming tool made to do such things.)

datetime to date??

When I send a query 'SELECT somedatecolumn FROM...' I got resultset
containing datetime. It's ok because it is a datetime field (or
smalldatetime) but it is a little frustrating because I'm interested only in
date. Is it possible to get only a date, without the time? My previous
database was in Visual FoxPro and there I had date columns but on MSDE or
MSSQL it seems I only can have datetime or smalldatetime.
Thx.
On Sat, 29 Jan 2005 20:04:47 +0100, Rutko wrote:

>When I send a query 'SELECT somedatecolumn FROM...' I got resultset
>containing datetime. It's ok because it is a datetime field (or
>smalldatetime) but it is a little frustrating because I'm interested only in
>date. Is it possible to get only a date, without the time? My previous
>database was in Visual FoxPro and there I had date columns but on MSDE or
>MSSQL it seems I only can have datetime or smalldatetime.
>Thx.
>
Hi Rutko,
The short answer: no.
The long answer: http://www.karaszi.com/SQLServer/info_datetime.asp
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||See the DATEPART functions in the Books On Line.
Jim
"Rutko" <rutko22001@.yahoo.com> wrote in message
news:egcU0VjBFHA.1040@.TK2MSFTNGP09.phx.gbl...
> When I send a query 'SELECT somedatecolumn FROM...' I got resultset
> containing datetime. It's ok because it is a datetime field (or
> smalldatetime) but it is a little frustrating because I'm interested only
> in
> date. Is it possible to get only a date, without the time? My previous
> database was in Visual FoxPro and there I had date columns but on MSDE or
> MSSQL it seems I only can have datetime or smalldatetime.
> Thx.
>

datetime query?

Hi,

This is my code:

SqlCommand getPreviousDateCmd = new SqlCommand("SELECT Top 1 Open_date FROM Counter WHERE (Open_date <= @.todays_date) ORDER BY Open_date DESC", myConnection);

//Get previous date
getPreviousDateCmd.Parameters.AddWithValue("@.todays_date", DateTime.Now.ToString("dd/MM/yyyy"));
DateTime previousDateTime =Convert.ToDateTime(getPreviousDateCmd.ExecuteScalar().ToString());

//previousDate = previousDateTime.ToString("dd/MM/yyyy");
LabelToday.Text = previousDateTime.ToString("dd/MM/yyyy");
//If previous date is same as today's date (Get only date and year)

I get a huge unstoppable(?) error message when it converts STRING to DATETIME.
What I want to do is get DATETIME object and change the format to "dd/MM/yyyy"

Could someone give me some ideas? Thankx!

Assuming your query always returns a row (*no* nulls e.g DBNull, when you would need to deal with that too), why converting it to string in the middle? Or converting at all if you know it is a date

DateTime previousDateTime = (DateTime)(getPreviousDateCmd.ExecuteScalar());

DateTime Query

Hi, am trying to build a scheduling system within my SQL Server application. Can someone point me in a good direction please?

OK, A user can select that they want something to happen Weekly, and on each Tuesday of every week. They of course can select any day from Monday through to Sunday. I would like to know how to take this data, and through a stored procedure update a table to set the "next execution date".

I have sorted the Daily timetable for each time, and the Monthly on a certain date seems easy enough, but I cant get the Weekly on a certain Day sorted. Any advice would be great!

Maybe you could post some code of your table and query...?

Though I'm not sure why you have multiple tables; monthly, weekly, daily.

You should just need one

NextExecutionMgr( DueDate datetime, FreqIntvl varchar(2), FreqAmt int, RecordKey varchar(200) )

index on DueDate, most likely a second index on RecordKey

The first item on your DueDate index is the next one to be processed.

When its time comes and once it is processesed you just adjust the date:

Code Snippet

case FreqIntvl when 'dy' then DueDate = DateAdd(dy, FreqAmt, DueDate)

when 'wk' then DueDate = DateAdd(wk, FreqAmt, DueDate)

etc.

end

(doesn't it suck that dateadd doesn't accept a variable for parameter one?)

|||

Why re-invent the wheel?

I would recommend exploring the SQL Agent Service, since it has full features calendaring and scheduling already built-in.

And if you are using SQL 2005 Express, which doens't include SQL Agent, you could explore a combination of using the Windows Scheduler service and SQLCmd.exe.

|||

Arnie, quite true.

I guess it just depends on what it is he's trying to schedule.

Agent is perfect for scheduled system level events and tasks.

But if he's trying to kick off application events with 1,000's of users, that a different thing.

Lotsa cats...

|||

And the skin just regrows...

I suspect that the solution will evolve into a combination of efforts -your outline about how to manage a 'queue' table, and some form of a scheduled process to 'POP' the queue.

There just isn't enough information to point the OP in the 'best' direction. SQL Agent, Notification Service, Service Broker Queues, some 'homegrown' hybrid, ...

Datetime problem

this is how the data stored in DB month/day/year hh:mm:ss

when i try to query it using :

select count(*) from table where convert(datetime, convert(varchar(10), datetime, 103),103) >= '01/03/2006'

Do i need to follow back ">=month/day/year" ? Since i have convert it to 103 ?

Thanks

How about doing it with a stored procedure?

DECLARE @.mydate_sm SMALLDATETIME
SET @.mydate_sm = '3/2/2006'

SELECT *
FROM dateTest
WHERE (datenow < @.mydate_sm)

|||You called your field "datetime"?sql

Datetime Parameter Format

Hi,all
I have a datetime parameter,
I use calendar to select value,but I want to format it as "yyyy-MM"
Any suggest?Can I use expression,how to ?
Kevin ChuJust get the value in as datetime format and use
format(Fields!xxx.Value,"yyyy-MM")
--
Tom Stude
"Kevin" wrote:
> Hi,all
> I have a datetime parameter,
> I use calendar to select value,but I want to format it as "yyyy-MM"
> Any suggest?Can I use expression,how to ?
> Kevin Chu
>|||=?Utf-8?B?VG9t?= <membership@.stude.no> wrote in news:ADC758F7-F6A7-4F56-
8C5C-1BAD643ABF30@.microsoft.com:
I want to show "yyyy-MM" format in preview,not get the value
> Just get the value in as datetime format and use
> format(Fields!xxx.Value,"yyyy-MM")
>

Datetime Parameter

Hi All,
I have a report with two parameters. Date1 and date2 and both are of type
datetime. when I select a date greater than 12/05/2006 the report fails
stating "the value provided for the report parameter Date2 is not valid for
its type". Now date1 has 01/05/2006 and date2 has 31/05/2006. For the
format is Australian date format and I have checked my regional settings and
they are set correctly to Australian and I have done the same on the report
server.
What is going on and how do I fix it....
Thanks
MichaelHi Michael,
Thank you for using MSDN Managed Newsgroup Support.
From you description, my understanding of this issue is: You want to
transfer the date-time parameter in (dd/mm/yyyy) format in to the report.
If I misunderstood your concern, please feel free to point it out.
By default, it's not possible to set the format of the date. However I did
a workaround for you, you can set the parameter data type to string then
use the SQL Convert() function to convert the parameter to DateTime.
The simple example of this will be like this.
Select * from orders where orderDate= CONVERT(DateTime, @.mydate, 103)
Here is the article about the CONVERT function.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
ca-co_2f3o.asp
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael/Wei
I have found the exact same problem, here's what we have discovered.
The problem is that RS swaps the month and day.
A start and end date is chosen in the datepicher controls. This is ok, but
when "view report" is activated, RS swicthes day and month and thus it is
considered a invalid date.
A clear example is when you e.g. choose 2006-01-05 in a datepicker control.
When view reports is activated, then the date has changed to 2006-05-01.
As an extra info, the problem is userspecific, we have tried to use
different users from the same computer and the result was ok with obe user
and not ok with another. The dateformat setup was 100 % simular on these
users.
The convert function is not the answer to this problem, instead I find it to
be a really annoying bug that makes the datepicker useless.
As Michael, I really would like the solution to this problem.
Forgive my english :-)
Martin
"Wei Lu" wrote:
> Hi Michael,
> Thank you for using MSDN Managed Newsgroup Support.
> From you description, my understanding of this issue is: You want to
> transfer the date-time parameter in (dd/mm/yyyy) format in to the report.
> If I misunderstood your concern, please feel free to point it out.
> By default, it's not possible to set the format of the date. However I did
> a workaround for you, you can set the parameter data type to string then
> use the SQL Convert() function to convert the parameter to DateTime.
> The simple example of this will be like this.
> Select * from orders where orderDate= CONVERT(DateTime, @.mydate, 103)
>
> Here is the article about the CONVERT function.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
> ca-co_2f3o.asp
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Martin,
Thank you for your post.
Unfortunately, I could not reproduct this issue on my side. When I click
the datetime picker, it could render the date correctly.
Would you please provide some additional information about the Regional
Settings?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei
My settings are as follows:
International settings/Standards and formats = "Danish"
Short dateformat = "DD-MM-YYYY"
Dateseperator = "-"
If you have an email I would be happy to send some screensdumps.
Sincerely,
Martin
"Wei Lu" wrote:
> Hi Martin,
> Thank you for your post.
> Unfortunately, I could not reproduct this issue on my side. When I click
> the datetime picker, it could render the date correctly.
> Would you please provide some additional information about the Regional
> Settings?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Martin,
My direct email address is weilu@.ONLINE.microsoft.com (Please remove the
ONLINE before you send the email).
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu -
I notice that you respond to certain posts "welcome to MSDN managed
newsgroup support" - how can I get this same assistance? I am an MSDN
subscriber - is there some special way to post a question to get your
attention? I am wondering how I can set a default date of TODAY in RS2005
when I have a date parameter set as datetime. I want to provide a default
value of current date. Cant seem to get it working without getting a type
incorrect when I use a function like TODAY or NOW. Thanks in advance!
"Wei Lu" wrote:
> Hi Martin,
> My direct email address is weilu@.ONLINE.microsoft.com (Please remove the
> ONLINE before you send the email).
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

datetime in in sql query

Hi

I am trying to write a query involve parameters. For example, the query:

Select * from myTable

wheremyDateTime=@.dt;

If I run the query, I was asked to enter value for the parameter. The query can be generated, however I can't save it, the error message says: Must declare the variable @.dt. When I tried to declare it, the system doesn't support it. I am using SQL Server Managerment Studio 2005.

I also tried the query without the parameter:

Select * from myTable

wheremyDateTime=31/07/2007;

But it didn't return record for any datetime format.

Could anyone help please? I just want to get some records filtered by a certain DateTime.

Claire

Are you trying to bulit it as a view or a stored procedure? Its not possible to create a View with paramters.

Stored Proc would look like:

CREATEPROCEDURE sp_MyStoredProc
@.dtasDateTime
AS

BEGIN

SELECT
*
FROM
myTable
WHERE
myDateTime=@.dt

END

To run it you wold have to execute it:

exec sp_MyStoredProc GetDate()

|||

Hi,

You will have to check how are the dates stored in your column. If they are stored as MM/dd/yyyy hh:mm:ss AMPM then you will have to use a Convert function as shown at the end of this post

For your first query, you will need to declare your variable using this

Declare @.dt datetime

Select * from myTable

wheremyDateTime=@.dt;

For your second query, if only the date is stored then

Select * from myTable

wheremyDateTime='31/07/2007'

To understand this better, try these

selectgetdate()

SELECTDATEADD(dd, 0,DATEDIFF(dd, 0,GETDATE()))

SELECTCONVERT(VARCHAR(10),GETDATE(),111)

Check this link

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

DateTime Format problem

In SQL query I have to find records which occour between two dates. I created Select query with two parameters @.date1 and @.date2 in clasue WHERE. But problem is with date format of my parameters. This format is to long. I dont wont to use time part of these parameters only date part is needed. When I put two identical dates my query doesn't find any data because both dates are eg. 2007-05-22 00:00:00. But I need data for all this day. How to correct this problem? Regards Pawel.

Use the Convert Function to convert it to a small date it will trim the time part

Where Convert(Varchar(10),@.Date1) = Convert(Varchar(10),@.Date2) ... Also you can use the third parameter in the Convert Function to get a specific format of dates i.e dd/mm/yyyy or yyyy/mm/dd etc. For a complete list

http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx

Check the link

|||

If the goal is to retrieve data for a single day, the method I prefer is lower inclusion, upper exclusion. Let me explain:

declare @.dtdatetime, @.startDatedatetime, @.endDatedatetime-- assume this is the dateset @.dt ='2007-01-02 12:34:56'select-- if only a date portion is passed into the sproc -- you won't need to remove the time portion @.startDate =convert(char(10), @.dt, 120) , @.endDate =dateadd(day, 1, @.startDate)select a.*-- use column list here!from tbl awhere-- inclusive of the lower limit a.DateColumn >= @.startDate-- exclusive of the upper limitand a.DateColumn < @.endDate

DateTime format

Hi in my table I have a field for storing date and datetime

i want to get date from that table using select query...

for eg select dt from tname...

Here i want to display only the date not with time.

And also time alone excluding with date...

ThanxHi ArunBala!

Convert function can be used for separating date and time from the datetime field. And for your reference use the link given below

DateTime Format Link
-----------
http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Here I have used table name satuserpost, fieldname Ndate

Query for selecting the date only from a datetime field
---------------------
select convert(varchar(10),Ndate,105)'My Date' from satuserpost

Query for selecting Time from a datetime field
----------------------
select convert(varchar(10),Ndate,108)'My Time' from satuserpost

All the Best

With Regards
Vijay. R|||Hi
Thanx for your reply

I have problem with it..
That is
select convert(varchar(10),Ndate,105)'My Date' from satuserpost where ndate='2007-12-15'

The above query returns no results...

WIthout where condition it is working. I want to select particular date or time..

How to do so?

Quote:

Originally Posted by VijaySofist

Hi ArunBala!

Convert function can be used for separating date and time from the datetime field. And for your reference use the link given below

DateTime Format Link
-----------
http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Here I have used table name satuserpost, fieldname Ndate

Query for selecting the date only from a datetime field
---------------------
select convert(varchar(10),Ndate,105)'My Date' from satuserpost

Query for selecting Time from a datetime field
----------------------
select convert(varchar(10),Ndate,108)'My Time' from satuserpost

All the Best

With Regards
Vijay. R

|||

Quote:

Originally Posted by arunbalait

Hi
Thanx for your reply

I have problem with it..
That is
select convert(varchar(10),Ndate,105)'My Date' from satuserpost where ndate='2007-12-15'

The above query returns no results...

WIthout where condition it is working. I want to select particular date or time..

How to do so?

your posted syntax is verifably correct and should be returning an italian (105) converted date type value as varchar
What does your data look like in SQL server when you return * from ndate do you have your field set as datetime datatype

select convert(varchar(10),Ndate,105) [My Date] from satuserpost where Ndate='2007-12-15'

Jim :)|||

Quote:

Originally Posted by Jim Doherty

your posted syntax is verifably correct and should be returning an italian (105) converted date type value as varchar
What does your data look like in SQL server when you return * from ndate do you have your field set as datetime datatype

select convert(varchar(10),Ndate,105) [My Date] from satuserpost where Ndate='2007-12-15'

Jim :)

Hi...

Thanks

Ya my data format is datetime datatype...

select convert(varchar(10),Ndate,105) [My Date] from satuserpost where Ndate='2007-12-15'

the above query gives 0 rows affected...

Help me plz... I want to take report on particular date... If i have given where condition it doesnt affect any values...

I want the result for

select username from mytable where dt='2007-12-18'

dt= field name in my table.. it is in datetime datatype..

thanx

Datetime convert problem

i am using select CONVERT(varchar(15),getdate(),101)
result is 1/23/2006 but i want result in 01/23/2005
what am i doing wrong. Any db settings?I'd suggest you use the ISO standard date 112 instead, eg:-
select CAST(CONVERT(char, GETDATE(), 112) AS datetime)
Here's why :-
SET language english
SELECT ISDATE('31 Jan 2005')
SELECT ISDATE('20050131')
GO
SET language french
SELECT ISDATE('31 Jan 2005')
SELECT ISDATE('20050131')
GO
HTH. Ryan
"Mubashir Khan" <m@.n.com> wrote in message
news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
>i am using select CONVERT(varchar(15),getdate(),101)
> result is 1/23/2006 but i want result in 01/23/2005
> what am i doing wrong. Any db settings?
>|||But i need date in MM/dd/yyyy format.
cant use 112 which is yyyyMMdd
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23v29kODIGHA.1188@.TK2MSFTNGP14.phx.gbl...
> I'd suggest you use the ISO standard date 112 instead, eg:-
> select CAST(CONVERT(char, GETDATE(), 112) AS datetime)
> Here's why :-
> SET language english
> SELECT ISDATE('31 Jan 2005')
> SELECT ISDATE('20050131')
> GO
>
> SET language french
> SELECT ISDATE('31 Jan 2005')
> SELECT ISDATE('20050131')
> GO
>
> --
> HTH. Ryan
> "Mubashir Khan" <m@.n.com> wrote in message
> news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
>|||In that case this may be of use to you :-
http://www.aspfaq.com/show.asp?id=2460
HTH. Ryan
"Mubashir Khan" <m@.n.com> wrote in message
news:Ox8PvWDIGHA.3944@.tk2msftngp13.phx.gbl...
> But i need date in MM/dd/yyyy format.
> cant use 112 which is yyyyMMdd
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23v29kODIGHA.1188@.TK2MSFTNGP14.phx.gbl...
>|||"Mubashir Khan" <m@.n.com> wrote in message
news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
>i am using select CONVERT(varchar(15),getdate(),101)
> result is 1/23/2006 but i want result in 01/23/2005
> what am i doing wrong. Any db settings?
It works on sql server 2000. Perhaps this is a problem with the tool you
are using to view the results.|||no use
<snip>
WHEN 'MM/DD/YYYY' THEN
CONVERT(CHAR(10), @.dt, 101)
</snip>
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Oh0EueDIGHA.216@.TK2MSFTNGP15.phx.gbl...
> In that case this may be of use to you :-
> http://www.aspfaq.com/show.asp?id=2460
> --
> HTH. Ryan
> "Mubashir Khan" <m@.n.com> wrote in message
> news:Ox8PvWDIGHA.3944@.tk2msftngp13.phx.gbl...
>|||it works fine on query analyzer
but it doesnot work when it is placed in a trigger.
trigger files it in some db table :'?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23SfFqgDIGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Mubashir Khan" <m@.n.com> wrote in message
> news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
> It works on sql server 2000. Perhaps this is a problem with the tool you
> are using to view the results.
>|||> it works fine on query analyzer
> but it doesnot work when it is placed in a trigger.
> trigger files it in some db table :'?
That wasn't the question you asked. The responses you have received assume
that you are having problems with the output formats - that, indeed, is not
the problem at all. Perhaps you need to read the following:
http://www.aspfaq.com/show.asp?id=2023
Tibor has some information that explains this in a format that is more
user-friendly, but it appears to be down. Have a look at it (with respect
to datetime values) when it comes back up.
http://www.karaszi.com/sqlserver/articles.asp
If that does not clear up the problem, then define "doesn't work".

DATETIME compare issue

I am trying to do a select where one set of date/time columns are greater than another. My date is stored in one column while the time is stored in another. Only the date portion of the date column is valid and only the time portion of the time column is valid.
Example my date column value is 8/1/2007 01:01:01 AM and my time column value is 1/1/2007 07:23:49 AM which in my system means the last update time was 8/1/2007 07:23:49 AM.

My select statment looks like this.

SELECT *
FROM CHANGE
WHERE
CONVERT(DATETIME( DATE_LAST_ALTERED, TIME_LAST_ALTERED)) < CONVERT(DATETIME(DATE_APPROVED,TIME_APPROVED))

I get an incorrect syntax near 'DATE_LAST_ALTERED'.

Am I totaly missing the point of DATETIME?

Matt

You need to give a look to the CAST AND CONVERT article in books online; your syntax for your CONVERT function is not correct. And yes, you might very well be missing the point of date and time in SQL Server. You should normally store date and time in a single column. And your usage here surely indicates that your date and time should be stored in a single column. You might be able to run with a where clause something like:

Code Snippet

where cast(floor(cast(date_last_altered as float)) as datetime)
+ time_last_altered
- floor(cast(time_last_altered) as float))
< cast(floor(cast(date_approved as float)) as datetime)
+ time_approved
- floor(cast(time_approved) as float))

Will someone please check me please?

|||Kent,

Thanks for the quick response. I was going to put a comment in about the fact that the 2 columns to store the data was not my doing and that I am stuck with it; knowing I would get that comment in response.

Apparently in DB2 DATETIME(col1,col2) is supported so this has never been an issue. I am also stuck with constraints on the length of my WHERE statement. (Imposed by the application that is taking the WHERE statement and storing it.)

Looks like I wil have to figure another way.

Thanks again.
Matt
|||

Very well; is this then a DB2 question and not a SQL Server question?

|||Can you modify/add a column to the database then? I would suggest u make a varchar column and combine the 2 columns, otherwise u may have to do a lot of number crunching due to ur where clause constraints - what is the exact contraint?

DateTime ?

This is my table structure

Date(m/dd/yyyy)

9/09/2006

I want to select month and year in the below format .

Sep 2006 .

How to do that ?

Try this..

SELECT CONVERT(CHAR(6),GETDATE(),109)

|||

Sorry Raghu,

Try this..

SELECT LEFT(CONVERT(CHAR(11),GETDATE(),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),GETDATE(),109),4)

|||

Or:

SELECT LEFT(DATENAME(month,'9/09/2006'),3) + ' ' + CONVERT(CHAR(4),Year('9/09/2006')) as DateYouwant

DateTime

PLZ help me!!!
i got to make a access frontend for a MSSQL server.
i need to select some stuff per week how can i make thet? PLZZ HELP I GOT
TO KNOW IT TOMAROW
TNX
Peter
If by "select some stuff per week" you mean calculate aggregates (sum,
min, max, count) per week, you can do something like this:
declare @.baseSunday datetime
set @.baseSunday = '19000107'
select
dateadd(week, datediff(week, @.baseSunday, OrderDate), @.baseSunday)
as WeekBeginning,
count(OrderID) as numOrdersThisWeek
from Northwind..Orders
group by dateadd(week, datediff(week, @.baseSunday, OrderDate), @.baseSunday)
order by dateadd(week, datediff(week, @.baseSunday, OrderDate), @.baseSunday)
go
If you want result rows for weeks that have no corresponding data in
your table, you can create an auxiliary table with all the Sundays you
would need and do
select
Sunday as WeekBeginning,
count(OrderID) as numOrdersThisWeek
from SundayTable left outer join yourDataTable
on yourDataTable.OrderDate >= Sunday
and yourDataTable.OrderDate < DateAdd(week, 1, Sunday)
group by Sunday
order by Sunday
A search of groups.google.com for sqlserver+group+week will probably
give you some other ideas.
Steve Kass
Drew University
Crazy Pete wrote:

>PLZ help me!!!
>i got to make a access frontend for a MSSQL server.
>i need to select some stuff per week how can i make thet? PLZZ HELP I GOT
>TO KNOW IT TOMAROW
>TNX
>Peter
>
>

datetime

create proc dbo.GetList
(
@.OrgList varchar(1000),
@.startDateTime datetime

)
as
begin

declare @.SQL varchar(1000)

set @.SQL = 'Select A.TransactionID,
A.PermitId,
A.IssuingOrganizationId,
A.VehicleId

From Vehicle A WITH (NOLOCK)
join PurchasingCompany B WITH (NOLOCK)
on A.PurchasingCompanyId = B.PurchasingCompanyId
Where
A.IssueDate >= '+ '@.startDateTime' +' And
A.IssuingOrganizationId IN ('+ @.OrgList+')'
exec(@.sql)
end
go

This doesn't work, But If I substitute@.startDateTime with '2/1/2004', it works. I think iam missing some formatting, I tried several ways to make it work. Could anyone tell me how I should do this.

You are including the literal "@.StartDateTime", which is clearly not what you want.

create proc dbo.GetList
(
@.OrgList varchar(1000),
@.startDateTime datetime

)
as
begin

Look at the BOL article on CONVERT to determine what format is right for you - I am using the ODBC canonical format.

declare @.SQL varchar(1000)

set @.SQL = 'Select A.TransactionID,
A.PermitId,
A.IssuingOrganizationId,
A.VehicleId

From Vehicle A WITH (NOLOCK)
join PurchasingCompany B WITH (NOLOCK)
on A.PurchasingCompanyId = B.PurchasingCompanyId
Where
A.IssueDate >= '''+ CONVERT(nvarchar(30),@.startDateTime,120) +''' And
A.IssuingOrganizationId IN ('+ @.OrgList+')'
exec(@.sql)
end
go

|||That really worked for me, thank you so much for the reply.

DATETIME

Below SQL works
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select top 10 * from mytable where mydate between @.startdate and @.enddate
But , below one error out with message
Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from mytable where mydate between ' + @.startdate +
' AND ' + @.enddate
execute @.sql
Here @.startdate and @.enddate are parameters and used inside a SP.
I have to use dynamic SQL for my logic and don't want to use CONVERT
function.
How to make this dynamic SQL work '
Thx
ShShamin,
the following code should work:
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from MyTable where MyTime between '''
+ cast(@.startdate as varchar) + ''' AND ''' + cast (@.enddate as varchar) +
''''
execute (@.sql)
hope this helps
Quentin
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#TgE1pwSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> Below SQL works
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select top 10 * from mytable where mydate between @.startdate and
@.enddate
> But , below one error out with message
> Server: Msg 241, Level 16, State 1, Line 4
> Syntax error converting datetime from character string.
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select @.sql = 'select * from mytable where mydate between ' + @.startdate
+
> ' AND ' + @.enddate
> execute @.sql
> Here @.startdate and @.enddate are parameters and used inside a SP.
> I have to use dynamic SQL for my logic and don't want to use CONVERT
> function.
> How to make this dynamic SQL work '
> Thx
> Sh
>
>

Dates and Parameters

I have created a report with parameters, I select the dates (01/05/2007) to
(01/07/07) using the calendar and then click view report the dates then reset
to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
are displayed in the report are returning the correct values. When you click
view report again without amending the dates the data is returned and the
parameters values as displayed in the report are incorrect. Please help.On Jul 6, 6:28 am, Changing Dates in reports & parameters <Changing
Dates in reports & paramet...@.discussions.microsoft.com> wrote:
> I have created a report with parameters, I select the dates (01/05/2007) to
> (01/07/07) using the calendar and then click view report the dates then reset
> to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
> are displayed in the report are returning the correct values. When you click
> view report again without amending the dates the data is returned and the
> parameters values as displayed in the report are incorrect. Please help.
You most likely want to check/change your computer's/server's regional
settings. This can be done through the control panel. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Checked regional settings on both the Server and my local pc and both are
British English.
thanks
lisa
"Changing Dates in reports & parameters" wrote:
> I have created a report with parameters, I select the dates (01/05/2007) to
> (01/07/07) using the calendar and then click view report the dates then reset
> to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
> are displayed in the report are returning the correct values. When you click
> view report again without amending the dates the data is returned and the
> parameters values as displayed in the report are incorrect. Please help.

Dates

Hi,

I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ReservationsConnectionString %>"

SelectCommand="SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = @.TIM_Time)">

<SelectParameters>

<asp:ControlParameter ControlID="Calendar1" Name="TIM_Time" PropertyName="SelectedDate"

Type="DateTime" />

</SelectParameters>

I get no records when I know they are there - do I have to put in additional checks to cater for the time component of the column?

Thanks in advance.

Assuming you can guarantee no time value on the @.TIM_Time parameter

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From]
FROM [Times]
WHERE ([TIM_Time] >= @.TIM_Time and [TIM_Time] < dateadd(day,1,@.TIM_Time))">

This gets all times from midnight on the day, to anything before midnight on the next day.

<light advice>I would seriously reconsider your naming convention of including a table prefix for every column, especially one that is an abbreviation. That will get seriously old over time having to type SOMETHING_ in front of every column, and then having to remove it with an alias everytime you want to display it to a user. </light advice>

|||

If TIM_Time contain only date (without time) then you can use this code

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = convert(varchar, @.TIM_Time, 112)

|||

Thaks for this but how does one hold only a date?

Is there a simple way of selecting the date part and the time part?

|||

you also can use Functions for this. You can do it in T-SQL (see following code), or write an equivalent with code behind.

declare @.dt as datetime, @.tm as datetime

select @.dt = getdate(), @.tm = getdate()

select @.tm, @.dt

select @.dt = dbo.FN_DATETIME_AS_HMS(@.dt)
select @.tm = dbo.FN_DATETIME_AS_DATE(@.tm)

select @.dt, @.tm

Here the functions code :

CREATE FUNCTION FN_DATETIME_AS_HMS (@.DT DATETIME)
RETURNS CHAR(8) AS
BEGIN
IF @.DT IS NULL RETURN NULL
DECLARE @.H INT
DECLARE @.M INT
DECLARE @.S INT
SET @.H = DATEPART(HOUR, @.DT)
SET @.M = DATEPART(MINUTE, @.DT)
SET @.S = DATEPART(SECOND, @.DT)
DECLARE @.RETVAL VARCHAR(8)
IF @.H < 10
SET @.RETVAL = '0' + CAST(@.H AS CHAR(1))+':'
ELSE
SET @.RETVAL = CAST(@.H AS CHAR(2))+':'
IF @.M < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.M AS CHAR(1))+':'
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.M AS CHAR(2))+':'
IF @.S < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.S AS CHAR(1))
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.S AS CHAR(2))
RETURN CAST(@.RETVAL AS CHAR(8))
END

--

CREATE FUNCTION FN_DATETIME_AS_DATE (@.DT DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CAST(FLOOR(CAST(@.DT AS FLOAT)) AS DATETIME)
END

ps : sources http://sqlpro.developpez.com/cours/sqlserver/udf

|||

Stephane,

Many thanks for this and sorry to be so ignorant but where exactly do I place the first part of your code? I created the 2 functions but I tried to place the first part it in a method but received an error message saying that the declare statement is not valid in a method.

|||

this is T-SQL only. it was just a sample to hava a look at the result. to run this code, execute it in Query Analyzer.

the important part is :

select dbo.FN_DATETIME_AS_HMS([Put a dateTime here])

and you'll have you time, with a 'null' date, fixed to 01/01/1900 and a valid hour. You also could choos another default value for null date. Some use 01/01/1753 with SQL Server.

select dbo.FN_DATETIME_AS_DATE([Put a DateTime here])

here, you'll have a date time with your correct date, and time set to 00:00:00.

|||

Stephane,

Thanks a lot, that was very helpful - I'm getting there - slowly!