Date from datetime ...

Hello NG,
we all know the problem of (date) equality checks on datetime fields
preventing us from issuing a command like:
SELECT * FROM [MyTable]
WHERE [SomeDateField] = @.SomeDateVariable
As datetime contains time information down to the milliseconds this query
will usually return nothing. The alternatives
SELECT * FROM [MyTable]
WHERE YEAR([SomeDateField]) = YEAR(@.SomeDateVariable)
AND MONTH([SomeDateField]) = MONTH(@.SomeDateVariable)
AND DAY([SomeDateField]) = DAY(@.SomeDateVariable)
SELECT * FROM [MyTable]
WHERE [SomeDateField] BETWEEN '20050510 000001' AND '20050510 235959'
are not really comfortable (the first one contains too much code, the second
one is not possible if you received the variable's content from somewhere
else). Has anyone come across a function that works like good ol' VB's
Date(), which delivers only the (complete) datepart (without the time part)?
Any hint would be greately appreciated.
Thanks in advance
ChristianNone of the alternatives you posted are good alternatives.
I've elaborated on the topic in http://www.karaszi.com/SQLServer/in...me.asp
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Christian Donner" <ChristianDonner@.discussions.microsoft.com> wrote in mess
age
news:609BD29D-3935-4099-BC64-0F14ABAD7237@.microsoft.com...
> Hello NG,
> we all know the problem of (date) equality checks on datetime fields
> preventing us from issuing a command like:
> SELECT * FROM [MyTable]
> WHERE [SomeDateField] = @.SomeDateVariable
> As datetime contains time information down to the milliseconds this query
> will usually return nothing. The alternatives
> SELECT * FROM [MyTable]
> WHERE YEAR([SomeDateField]) = YEAR(@.SomeDateVariable)
> AND MONTH([SomeDateField]) = MONTH(@.SomeDateVariable)
> AND DAY([SomeDateField]) = DAY(@.SomeDateVariable)
> SELECT * FROM [MyTable]
> WHERE [SomeDateField] BETWEEN '20050510 000001' AND '20050510 235959'
> are not really comfortable (the first one contains too much code, the seco
nd
> one is not possible if you received the variable's content from somewhere
> else). Has anyone come across a function that works like good ol' VB's
> Date(), which delivers only the (complete) datepart (without the time part
)?
> Any hint would be greately appreciated.
> Thanks in advance
> Christian|||This is my favorite method for stripping time from date:
-- Strip time from today's date
SELECT DATEADD( day, DATEDIFF( day, 0, GETDATE() ), 0 )
Where GETDATE() can be substituted for your date. Credit to Steve Kass.
Damien
"Christian Donner" wrote:

> Hello NG,
> we all know the problem of (date) equality checks on datetime fields
> preventing us from issuing a command like:
> SELECT * FROM [MyTable]
> WHERE [SomeDateField] = @.SomeDateVariable
> As datetime contains time information down to the milliseconds this query
> will usually return nothing. The alternatives
> SELECT * FROM [MyTable]
> WHERE YEAR([SomeDateField]) = YEAR(@.SomeDateVariable)
> AND MONTH([SomeDateField]) = MONTH(@.SomeDateVariable)
> AND DAY([SomeDateField]) = DAY(@.SomeDateVariable)
> SELECT * FROM [MyTable]
> WHERE [SomeDateField] BETWEEN '20050510 000001' AND '20050510 235959'
> are not really comfortable (the first one contains too much code, the seco
nd
> one is not possible if you received the variable's content from somewhere
> else). Has anyone come across a function that works like good ol' VB's
> Date(), which delivers only the (complete) datepart (without the time part
)?
> Any hint would be greately appreciated.
> Thanks in advance
> Christian|||"Tibor Karaszi" wrote:
> None of the alternatives you posted are good alternatives.
> I've elaborated on the topic in [url]http://www.karaszi.com/SQLServer/info_datetime.asp.[/url
]
That's not exactely what I was searching for, but it contains very valuable
hints for the solution (the result is somewhat serendipitious ;-). Thank you
very much for your help!|||"Damien" wrote:
> This is my favorite method for stripping time from date:
> -- Strip time from today's date
> SELECT DATEADD( day, DATEDIFF( day, 0, GETDATE() ), 0 )
> Where GETDATE() can be substituted for your date. Credit to Steve Kass.
> Damien
Nice idea. Thanks!|||I'm glad you found it helpful (and meanwhile I learned a new English word ;-
) ).
Now that you read (parts of) the article, I take it that the rest is rather
simple. Convert to a
character string using code 112. Then use the > AND <= (plus one day) techni
que.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Christian Donner" <ChristianDonner@.discussions.microsoft.com> wrote in mess
age
news:FBCBC7B9-CA55-4851-9C95-6EF472ABA8F7@.microsoft.com...
> "Tibor Karaszi" wrote:
> That's not exactely what I was searching for, but it contains very valuabl
e
> hints for the solution (the result is somewhat serendipitious ;-). Thank y
ou
> very much for your help!|||CREATE function dbo.TimeStrip (@.dt datetime)
returns datetime
AS
begin
return convert(datetime, convert(varchar(10), @.dt, 102), 102)
end
GO
select dbo.TimeStrip ( getdate())
select dbo.TimeStrip ( null )
****************************************
************************
Tapio Kulmala
"Those are my principles. If you don't like them I have others."
- Groucho Marx
****************************************
************************|||"Tapio Kulmala" wrote:

> CREATE function dbo.TimeStrip (@.dt datetime)
> returns datetime
> AS
> begin
> return convert(datetime, convert(varchar(10), @.dt, 102), 102)
> end
> GO
> select dbo.TimeStrip ( getdate())
> select dbo.TimeStrip ( null )
> Tapio Kulmala
This way I loose access to the index (as Tibor pointed out in his excellent
article). Thank you for the suggestion.

DATE FORMAT/SYNTAX QUESTION

What getdate() syntax command can give me time in the following format:

10:41:55 AM

Regards,

Addi"addi" <addi_s@.hotmail.com> wrote in message news:llnGb.31580$zU5.19873@.news01.roc.ny...
> What getdate() syntax command can give me time in the following format:
> 10:41:55 AM
> Regards,
> Addi

SELECT CAST(((DATEPART(HOUR, CURRENT_TIMESTAMP) + 11) % 12) + 1
AS VARCHAR(2)) +
':' +
RIGHT('0' + DATENAME(MINUTE, CURRENT_TIMESTAMP), 2) +
':' +
RIGHT('0' + DATENAME(SECOND, CURRENT_TIMESTAMP), 2) +
CASE WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 12
THEN ' AM'
ELSE ' PM'
END AS current_time

current_time
5:22:57 PM

Regards,
jag|||Note that John's solution may be ugly but remember that SQL is not a
report-writing language. It's usually better to format results in the
front-end. Many languages provide robust date formatting functions.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"addi" <addi_s@.hotmail.com> wrote in message
news:llnGb.31580$zU5.19873@.news01.roc.ny...
> What getdate() syntax command can give me time in the following format:
> 10:41:55 AM
> Regards,
> Addi

Date Format accepting MDY instead of DMY

I have a unbound text box that gets its date from the system (date.now).

The problem is that when I write it to SQL (via a SQL Insert Command) It throws an error.

It transpires that the format is wrong. It accepts the date such as 07/17/2007 just fine but not 17/07/2007 which is automatically generated.

My IIS has a locale setting with is correct for the UK.

How can I change SQL 2005 so that it accepts DMY for a date/time field

Thanks.

Hmmm,

I actually fixed this already. As my date box was to be updated by the system clock and not the user I thought the easiest thing would be to format the date in US format before submission.

I used this VB code:

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

'Create a var. named rightNow and set it to the current date/time

Dim rightNowAs DateTime =Date.Now

Dim sAsString'create a string

s = rightNow.ToString("MM/dd/yyyy hh:mm:ss")

LogDate.Text = s

EndSub

That has done the trick.

But would like to know how to change SQL anyway.

|||

http://msdn2.microsoft.com/en-us/library/aa259188(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/ms174398.aspx -- probably better

If you search hard enough, there are a couple of ways of specifying it in your connection string as well. I believe (but could be wrong) that it's like:

Persist Security Info=False;Trusted_Connection=True;database=AdventureWorks;server=(local);Language=British

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
Gun
See my reply in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
GunSee my reply in .programming. Please don't multi-post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>|||Hi
Read more on convert/cast functions in sql bol
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gunawan" <jgun98.milis@.gmail.com> wrote in message
news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
GunSee my reply in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.g
bl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>|||Hi
Read more on convert/cast functions in sql bol
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gunawan" <jgun98.milis@.gmail.com> wrote in message
news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>