Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select from GETDATE()? Expand / Collapse
Author
Message
Posted Wednesday, September 24, 2008 5:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309
Dear All,
When you run the following command:
SELECT GETDATE() AS [CurrentDateTime]
The Result is : 2008-09-24 12:47:11.587

The question is: How can I get the following result from the above query:
20080924
Post #575039
Posted Wednesday, September 24, 2008 5:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 6,783, Visits: 12,893
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '')



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #575042
Posted Wednesday, September 24, 2008 5:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
obarahmeh (9/24/2008)
Dear All,
When you run the following command:
SELECT GETDATE() AS [CurrentDateTime]
The Result is : 2008-09-24 12:47:11.587

The question is: How can I get the following result from the above query:
20080924

Where do you want to show/use formatted dates?

If it is only for display

SELECT convert(varchar(8),getdate(),112)





Madhivanan

Failing to plan is Planning to fail
Post #575045
Posted Wednesday, September 24, 2008 5:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
Chris Morris (9/24/2008)
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '')


You dont need additional replace function
See my first reply :)




Madhivanan

Failing to plan is Planning to fail
Post #575048
Posted Wednesday, September 24, 2008 5:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309
Thank you very much...Both of you correct.
Thanks again
Post #575053
Posted Wednesday, September 24, 2008 5:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
obarahmeh (9/24/2008)
Thank you very much...Both of you correct.
Thanks again

Well. You didnt answer to my question ;)




Madhivanan

Failing to plan is Planning to fail
Post #575057
Posted Wednesday, September 24, 2008 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 6,783, Visits: 12,893
Madhivanan, quite right! I don't know why the conversion I posted is being used - possibly from a previous version. It's used extensively here to generate a suffix for export filenames.

Thanks

ChrisM


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #575060
Posted Wednesday, September 24, 2008 5:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309
well, I have a trigger that reads the latest inserted data and insert them into another table. I've wrote the trigger, then it inserted all data from the first table, where I need only the daily data inserted. and so, I've added a condition where date = today date.
and I am checking that now.
Post #575065
Posted Wednesday, September 24, 2008 5:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
obarahmeh (9/24/2008)
well, I have a trigger that reads the latest inserted data and insert them into another table. I've wrote the trigger, then it inserted all data from the first table, where I need only the daily data inserted. and so, I've added a condition where date = today date.
and I am checking that now.

If you want to find out today's data, use
where	datecol>=dateadd(day,datediff(day,0,getdate()),0) and
datecol<dateadd(day,datediff(day,0,getdate()),1)





Madhivanan

Failing to plan is Planning to fail
Post #575069
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse